clickhouse - 如何在最终查询中添加包含空数据的行?
问题描述
我有一张数据表:
table1
country date price
USA 2001-01-25 2
RUS 2001-01-25 17
GER 2001-01-25 30
USA 2001-02-25 11
RUS 2001-02-25 22
RUS 2001-02-26 25
我可以让所有国家
SELECT DISTINCT country FROM table1;
country
USA
RUS
GER
并获取当月的所有价格
SELECT sum(price), country FROM table1 WHERE date >= '2001-02-01' AND date < '2001-03-01' GROUP BY country, price;
sum(price) country
11 USA
47 RUS
但我也想看到国家“GER”的一行
sum(price) country
11 USA
47 RUS
0 GER
如何在 Clickhouse 中轻松实现?
解决方案
使用条件聚合:
SELECT
country,
SUM(CASE WHEN date >= '2001-02-01' AND date < '2001-03-01'
THEN price ELSE 0 END) AS prices
FROM table1
GROUP BY country;
您当前WHERE
条款的问题在于,它将过滤掉 2001 年 2 月期间根本没有匹配价格的国家/地区。
如果上述解决方案不能执行,我们可以将其编写为包含所有国家/地区的表的连接table1
:
SELECT c.country, COALESCE(t.prices, 0) AS prices
FROM (SELECT DISTINCT country FROM table1) c
LEFT JOIN
(
SELECT country, SUM(prices) AS prices
FROM table1
WHERE date >= '2001-02-01' AND date < '2001-03-01'
GROUP BY country
) t
ON c.country = t.country;
推荐阅读
- python - 使用analyzer ='char'如何计算Tf-Idf值?
- html - SVG Path representing line not showing with working and non working examples
- powershell - Run a external program with parameters and wait for it to end in Powershell
- hadoop-yarn - 尽管有可用内存,YARN 作业仍卡在 ACCEPTED 状态
- image - How to make text appear near the bottom of an image in React Native?
- xcode - Perform actions on a certain window of the application with AppleScript
- esp8266 - How to retrieve the flashed binary in ESP8266
- r - R:apply() 中的错误?
- django - show and create object from many2one relation in related model's template
- javascript - Adonis.js populate all with related tables data