问题描述:

I have the following data in a single table. vc is varchar

eq_time eq_latitude eq_longitude eq_depth eq_mag eq_magType eq_nst eq_gap eq_dmin eq_rms eq_net eq_id eq_updated eq_place eq_type

timestamp double double double double vc(20) double double double double vc(100) vc(20) timestamp vc(100) vc(20)

"2015-02-19 06:32:53" 33.9585 -116.9558333 12.35 0.74 ml 16 72 0.1357 0.22 ci ci37318664 "2015-02-19 06:36:54" "4km NNE of Beaumont California" earthquake

"2015-02-19 06:07:18" 38.7946663 -122.7791672 3.78 0.55 md 6 161 0.01831 0 nc nc72397170 "2015-02-19 06:41:05" "2km NW of The Geysers California" earthquake

"2015-02-19 06:07:04" 38.7923317 -122.7785034 3.75 1.01 md 20 74 0.007406 0.03 nc nc72397165 "2015-02-19 06:51:05" "2km NW of The Geysers California" earthquake

"2015-02-19 06:03:26" -4.9889 101.8933 31.96 4.6 mb 0 179 1.229 0.83 us usc000tre3 "2015-02-19 06:51:21" "137km SSW of Bengkulu Indonesia" earthquake

"2015-02-19 05:59:26" 38.5807 -118.4577 5.63 1.44 ml 10 193.63 0.621 0 nn nn00483562 "2015-02-19 06:02:59" "15km ENE of Hawthorne Nevada" earthquake

"2015-02-19 05:55:55" 65.8149 -149.7911 0.1 1.6 ml 0 0 0 0.88 ak ak11512985 "2015-02-19 06:30:52" "98km NNE of Manley Hot Springs Alaska" earthquake

"2015-02-19 05:52:44" 38.8250008 -122.8448334 2.31 0.57 md 8 167 0.007886 0.01 nc nc72397155 "2015-02-19 06:23:03" "9km NW of The Geysers California" earthquake

"2015-02-19 05:45:02" 35.5811667 -118.474 9.46 0.71 ml 13 86 0.08144 0.12 ci ci37318648 "2015-02-19 05:49:06" "2km ESE of Bodfish California" earthquake

"2015-02-19 05:39:25" 35.8693 - 116.6933 7 0.41 ml 6 274.08 0.196 0 nn nn00483561 "2015-02-19 05:41:23" "65km E of Searles Valley California" earthquake

"2015-02-19 05:36:57" 35.9223328 -120.4726639 5.29 2.37 md 41 46 0.02433 0.07 nc nc72397150 "2015-02-19 06:02:03" "26km SSW of Coalinga California" earthquake

Similarly, there are 8700 rows. I want to find out the occurences per week per magnitude range. The final data should look as follows.

Week 2-2.99 3-3.99 4-4.99 5-5.99

1 10 2 4 6

2 1 3 0 8

3 9 1 7 1

4 7 9 1 0

I have tried the following query, but I am missing a lot of data.

SELECT rs.eq_week

FROM (

SELECT CASE

WHEN eq_mag between 2 and 2.99

THEN 'Week 1'

from usaeq) RS

Group By rs.eq_week

网友答案:

I think this is a easy way, although the column headers are not quite as in your example:

TRANSFORM Count(eq.eq_time) AS Count_occur
SELECT eq.eq_week
FROM eq
GROUP BY eq.eq_week
PIVOT CONVERT(INTEGER, eq_mag);
网友答案:

Found the answer.

select 
    week, count(mag2) as mag2, count(mag3) as mag3, count(mag4) as mag4, count(mag5) as mag5
from
    ((select 
        case
                when date(eq_time) between cast('2015-01-20' as date) and cast('2015-01-26' as date) then 1
                when date(eq_time) between cast('2015-01-27' as date) and cast('2015-02-02' as date) then 2
                when date(eq_time) between cast('2015-02-03' as date) and cast('2015-02-09' as date) then 3
                when date(eq_time) between cast('2015-02-10' as date) and cast('2015-02-16' as date) then 4
                when date(eq_time) between cast('2015-02-17' as date) and cast('2015-02-23' as date) then 5
                when date(eq_time) between cast('2015-02-24' as date) and cast('2015-02-29' as date) then 6
            end week,
            usaeq.eq_id
    from
        usaeq) as week, (select 
        case
                when eq_mag between 2 and 2.99 then eq_mag
            end mag2,
            usaeq.eq_id
    from
        usaeq) as mag2, (select 
        case
                when eq_mag between 3 and 3.99 then eq_mag
            end mag3,
            usaeq.eq_id
    from
        usaeq) as mag3, (select 
        case
                when eq_mag between 4 and 4.99 then eq_mag
            end mag4,
            usaeq.eq_id
    from
        usaeq) as mag4, (select 
        case
                when eq_mag >= 5 then eq_mag
            end mag5,
            usaeq.eq_id
    from
        usaeq) as mag5)
where
    week.eq_id = mag2.eq_id and
    week.eq_id = mag3.eq_id and
    week.eq_id = mag4.eq_id and
    week.eq_id = mag5.eq_id 
group by week;

After running the query, the data looks as follows.

week mag2 mag3 mag4 mag5
1    259  69   129  19
2    315  162  132  41
3    286  94   87   19
4    259  64   83   27
5    59   11   26   17
网友答案:

It's not all that complicated. In fact, it's a fairly standard pattern found in a lot of queries. Just a series of CASE statements with SUMs for each group you want. Here's a simple version based on the data you give. I've even added extra groups.

WITH usaeq( eq_time, eq_mag )AS(
    SELECT '2015-02-19', 0.74 UNION ALL
    SELECT '2015-02-19', 0.55 UNION ALL
    SELECT '2015-02-19', 1.01 UNION ALL
    SELECT '2015-02-19', 4.60 UNION ALL
    SELECT '2015-02-19', 1.44 UNION ALL
    SELECT '2015-02-19', 1.60 UNION ALL
    SELECT '2015-02-19', 0.57 UNION ALL
    SELECT '2015-02-19', 0.71 UNION ALL
    SELECT '2015-02-19', 0.41 UNION ALL
    SELECT '2015-02-19', 2.37
)
SELECT  DatePart( WW, eq_time ) Week,
        Sum( case when eq_mag >= 0.0 and eq_mag < 1 then 1 else 0 end ) as "0-0.99",
        Sum( case when eq_mag >= 1.0 and eq_mag < 2 then 1 else 0 end ) as "1-1.99",
        Sum( case when eq_mag >= 2.0 and eq_mag < 3 then 1 else 0 end ) as "2-2.99",
        Sum( case when eq_mag >= 3.0 and eq_mag < 4 then 1 else 0 end ) as "3-3.99",
        Sum( case when eq_mag >= 4.0 and eq_mag < 5 then 1 else 0 end ) as "4-4.99",
        Sum( case when eq_mag >= 5.0 and eq_mag < 6 then 1 else 0 end ) as "5-5.99"
FROM    usaeq
group by DatePart( WW, eq_time );
相关阅读:
Top