时间戳

首页 > 解决方案 > SQL查询多个选择语句

sql - SQL查询多个选择语句

问题描述

我有一个数据框，其中有很多类似于下图左侧的表格。我将使用 SQL 对其进行查询，以获得类似于下表所示的结果。这样我就可以绘制一个堆积条形图，其中每个条形代表一个状态和严重性计数 S03、S04 的数据将加起来。

+--+-----+--------+
|ID|State|Severity|
+--+-----+--------+
|01| NY  | 3      |        +-----+---+---+
|02| CA  | 4      |        |State|S03|S04|
|03| NY  | 4      |    =>  +-----+---+---+
|04| CA  | 3      |        | CA  | 1 | 3 |
|05| CA  | 4      |        | NY  | 1 | 1 |
|06| CA  | 4      |

我尝试了以下 SQL 查询，但它对 S03 中的每个条目给出相同的结果，对 S04 给出相同的结果。

city_accidents = spark.sql("\
    SELECT State, \
    (SELECT COUNT(ID) AS Count FROM us_accidents WHERE Severity = 3 ) AS S03, \
    (SELECT COUNT(ID) AS Count FROM us_accidents WHERE Severity = 4 ) AS S04 \
    FROM accidents \
    GROUP BY State \
    ORDER BY State DESC LIMIT 10")
city_accidents.show()

+-----+---+---+
|State|S03|S04|
+-----+---+---+
| NY  | 1 | 3 |
| CA  | 1 | 3 |

这可能是因为我没有为要从中选择状态的内部选择语句输入任何过滤器。有没有办法可以在选择查询中访问这些内部变量？我的意思是如果我可以将内部选择语句更改为(SELECT COUNT(ID) AS Count FROM us_accidents WHERE Severity = 3 AND State = this.State ) AS S03..

标签： sql

解决方案

SELECT State,
       sum(case when Severity = 3 then 1 else 0 end) AS S03,
       sum(case when Severity = 4 then 1 else 0 end) AS S04
FROM accidents 
GROUP BY State 
ORDER BY State DESC 
LIMIT 10

推荐阅读