英文:
Clickhouse fill empty rows with next non-empty value
问题
我有一个类似这样的表格
日期 | 编号 | 价格 | 期望的前一个值 | 期望的下一个值 |
---|---|---|---|---|
2019-08-17 | 1 | 5 | 5 | 5 |
2019-08-17 | 2 | 15.4 | 15.4 | 15.4 |
2019-08-18 | 1 | 0 | 5 | 5.6 |
2019-08-18 | 2 | 0 | 15.4 | 14 |
2019-08-19 | 1 | 0 | 5 | 5.6 |
2019-08-19 | 2 | 0 | 15.4 | 14 |
2019-08-20 | 1 | 0 | 5 | 5.6 |
2019-08-20 | 2 | 0 | 15.4 | 14 |
2019-08-21 | 1 | 5.6 | 5.6 | 5.6 |
2019-08-21 | 2 | 14 | 14 | 14 |
这是以下查询的结果:
SELECT a.date AS date,
a.id AS id,
p.price AS price
FROM articles a
LEFT JOIN (SELECT date,
id,
price
FROM prices) p
ON a.date = p.date
AND a.id = p.id
因为价格表不包含所有日期的数据。我该如何在ClickHouse中使用前一个或下一个非零值来填充零价格值?
版本号是22.8.9.24。
英文:
I have a table like this
date | id | price | desired previous value | desired next value |
---|---|---|---|---|
2019-08-17 | 1 | 5 | 5 | 5 |
2019-08-17 | 2 | 15.4 | 15.4 | 15.4 |
2019-08-18 | 1 | 0 | 5 | 5.6 |
2019-08-18 | 2 | 0 | 15.4 | 14 |
2019-08-19 | 1 | 0 | 5 | 5.6 |
2019-08-19 | 2 | 0 | 15.4 | 14 |
2019-08-20 | 1 | 0 | 5 | 5.6 |
2019-08-20 | 2 | 0 | 15.4 | 14 |
2019-08-21 | 1 | 5.6 | 5.6 | 5.6 |
2019-08-21 | 2 | 14 | 14 | 14 |
Its a result of this query:
SELECT a.date AS date,
a.id AS id,
p.price AS price
FROM articles a
LEFT JOIN (SELECT date,
id,
price
FROM prices) p
ON a.date = p.date
AND a.id = p.id
because prices table does not have data for all dates.
How can I fill zero price values with next or previous non zero value in clickhouse?
ver is 22.8.9.24
答案1
得分: 1
请尝试以下代码:
WITH T AS
(
SELECT c1 as date, c2 as id, c3 as price
FROM VALUES
(
(toDate ('2019-08-17'), 1, 5)
, (toDate ('2019-08-17'), 2, 15.4)
, (toDate ('2019-08-18'), 1, 0)
, (toDate ('2019-08-18'), 2, 0)
, (toDate ('2019-08-19'), 1, 0)
, (toDate ('2019-08-19'), 2, 0)
, (toDate ('2019-08-20'), 1, 0)
, (toDate ('2019-08-20'), 2, 0)
, (toDate ('2019-08-21'), 1, 5.6)
, (toDate ('2019-08-21'), 2, 14)
)
)
SELECT
*
, last_value (nullif (price, 0)) over (partition by id order by date)
as prev_value
, last_value (nullif (price, 0)) over (partition by id order by date desc)
as next_value
FROM T
ORDER BY date, id
date | id | price | prev_value | next_value |
---|---|---|---|---|
2019-08-17 | 1 | 5.0 | 5.0 | 5.0 |
2019-08-17 | 2 | 15.4 | 15.4 | 15.4 |
2019-08-18 | 1 | 0.0 | 5.0 | 5.6 |
2019-08-18 | 2 | 0.0 | 15.4 | 14.0 |
2019-08-19 | 1 | 0.0 | 5.0 | 5.6 |
2019-08-19 | 2 | 0.0 | 15.4 | 14.0 |
2019-08-20 | 1 | 0.0 | 5.0 | 5.6 |
2019-08-20 | 2 | 0.0 | 15.4 | 14.0 |
2019-08-21 | 1 | 5.6 | 5.6 | 5.6 |
2019-08-21 | 2 | 14.0 | 14.0 | 14.0 |
英文:
Try this:
WITH T AS
(
SELECT c1 as date, c2 as id, c3 as price
FROM VALUES
(
(toDate ('2019-08-17'), 1, 5)
, (toDate ('2019-08-17'), 2, 15.4)
, (toDate ('2019-08-18'), 1, 0)
, (toDate ('2019-08-18'), 2, 0)
, (toDate ('2019-08-19'), 1, 0)
, (toDate ('2019-08-19'), 2, 0)
, (toDate ('2019-08-20'), 1, 0)
, (toDate ('2019-08-20'), 2, 0)
, (toDate ('2019-08-21'), 1, 5.6)
, (toDate ('2019-08-21'), 2, 14)
)
)
SELECT
*
, last_value (nullif (price, 0)) over (partition by id order by date)
as prev_value
, last_value (nullif (price, 0)) over (partition by id order by date desc)
as next_value
FROM T
ORDER BY date, id
date | id | price | prev_value | next_value |
---|---|---|---|---|
2019-08-17 | 1 | 5.0 | 5.0 | 5.0 |
2019-08-17 | 2 | 15.4 | 15.4 | 15.4 |
2019-08-18 | 1 | 0.0 | 5.0 | 5.6 |
2019-08-18 | 2 | 0.0 | 15.4 | 14.0 |
2019-08-19 | 1 | 0.0 | 5.0 | 5.6 |
2019-08-19 | 2 | 0.0 | 15.4 | 14.0 |
2019-08-20 | 1 | 0.0 | 5.0 | 5.6 |
2019-08-20 | 2 | 0.0 | 15.4 | 14.0 |
2019-08-21 | 1 | 5.6 | 5.6 | 5.6 |
2019-08-21 | 2 | 14.0 | 14.0 | 14.0 |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论