SQL查询多个选择语句

Question

我有一个数据框，它有很多类似于下面左侧表格的内容。我是使用 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 语句输入任何过滤器。有没有办法可以在选择查询中访问这些内部变量？我的意思是如果我可以将内部选择语句更改为 (SELECT COUNT(ID) AS Count FROM us_accidents WHERE Severity = 3 AND State = this.State ) AS S03 ..

Answer 1

您可以尝试以下方式 -

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