用下一个非空值填充ClickHouse中的空行。

huangapple go评论68阅读模式
英文:

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

huangapple
  • 本文由 发表于 2023年7月27日 19:57:20
  • 转载请务必保留本文链接:https://go.coder-hub.com/76779495.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定