python - SQL 用于组合字段中的数据并提供整理后的数据

Question

我在一个表中有以下数据。 vc是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

同样，有 8700 行。我想找出每个震级范围每周发生的次数。最终数据应如下所示。

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

我尝试了以下查询，但丢失了很多数据。

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

Answer 1

我认为这是一种简单的方法，尽管列标题与您的示例中的不太一样:

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