The lagInFrame function doesn't seem to work properly in materialized views, it got 0.
VERSION ClickHouse 23.3.1.2823
My data is:
| code | datetime | volume | total_turnover |
|---|---|---|---|
| 000001.XSHE | 20240318112830000 | 102847384 | 1084539136 |
| 000001.XSHE | 20240318112827000 | 102842184 | 1084484224 |
| 000001.XSHE | 20240318112824000 | 102840688 | 1084468480 |
| 000001.XSHE | 20240318112821000 | 102816888 | 1084217856 |
| 000001.XSHE | 20240318112818000 | 102815088 | 1084198784 |
| 000001.XSHE | 20240318112815000 | 102805584 | 1084098816 |
| 000001.XSHE | 20240318112812000 | 102798488 | 1084023936 |
| 000001.XSHE | 20240318112809000 | 102797184 | 1084010240 |
| 000001.XSHE | 20240318112806000 | 102791784 | 1083953408 |
When I run this sql it works fine
SELECT
code
, parseDateTimeBestEffort(substring(toString(`datetime`), 1, 14)) AS `datetime`
, total_turnover
, volume
, lagInFrame(total_turnover) OVER w AS prev_total_turnover
, lagInFrame(volume) OVER w AS prev_volume
FROM stock_market.stock_ticks_kfk
WINDOW w AS (PARTITION BY code ORDER BY code ASC, `datetime` ASC ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING)
| code | datetime | total_turnover | volume | prev_total_turnover | prev_volume |
|---|---|---|---|---|---|
| 000001.XSHE | 2024-03-18 11:28:30 | 1084539136 | 102847384 | 1084484224 | 102842184 |
| 000001.XSHE | 2024-03-18 11:28:27 | 1084484224 | 102842184 | 1084468480 | 102840688 |
| 000001.XSHE | 2024-03-18 11:28:24 | 1084468480 | 102840688 | 1084217856 | 102816888 |
| 000001.XSHE | 2024-03-18 11:28:21 | 1084217856 | 102816888 | 1084198784 | 102815088 |
| 000001.XSHE | 2024-03-18 11:28:18 | 1084198784 | 102815088 | 1084098816 | 102805584 |
| 000001.XSHE | 2024-03-18 11:28:15 | 1084098816 | 102805584 | 1084023936 | 102798488 |
| 000001.XSHE | 2024-03-18 11:28:12 | 1084023936 | 102798488 | 1084010240 | 102797184 |
| 000001.XSHE | 2024-03-18 11:28:09 | 1084010240 | 102797184 | 1083953408 | 102791784 |
But it doesn't work properly when I run in a materialized view
CREATE TABLE table_xxx
(
`code` String,
`datetime` DateTime,
`total_turnover` Float32,
`volume` UInt32,
`prev_total_turnover` Float32,
`prev_volume` UInt32
)
ENGINE = MergeTree
ORDER BY (code, `datetime`)
TTL `datetime` + INTERVAL 1 DAY;
CREATE MATERIALIZED VIEW view_xxx TO table_xxx
AS
SELECT
code
, parseDateTimeBestEffort(substring(toString(`datetime`), 1, 14)) AS `datetime`
, total_turnover
, volume
, lagInFrame(total_turnover) OVER w AS prev_total_turnover
, lagInFrame(volume) OVER w AS prev_volume
FROM table_xxx_0
WINDOW w AS (PARTITION BY code ORDER BY code ASC, `datetime` ASC ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING)
| code | datetime | total_turnover | volume | prev_total_turnover | prev_volume |
|---|---|---|---|---|---|
| 000001.XSHE | 2024-03-18 11:28:30 | 1084539136 | 102847384 | 1084484224 | 102842184 |
| 000001.XSHE | 2024-03-18 11:28:27 | 1084484224 | 102842184 | 1084468480 | 102840688 |
| 000001.XSHE | 2024-03-18 11:28:24 | 1084468480 | 102840688 | 0.0 | 0 |
| 000001.XSHE | 2024-03-18 11:28:21 | 1084217856 | 102816888 | 1084198784 | 102815088 |
| 000001.XSHE | 2024-03-18 11:28:18 | 1084198784 | 102815088 | 0.0 | 0 |
| 000001.XSHE | 2024-03-18 11:28:15 | 1084098816 | 102805584 | 1084023936 | 102798488 |
| 000001.XSHE | 2024-03-18 11:28:12 | 1084023936 | 102798488 | 1084010240 | 102797184 |
| 000001.XSHE | 2024-03-18 11:28:09 | 1084010240 | 102797184 | 0.0 | 0 |
When I use the any() function, the row that got 0 before will get the value of its own row instead of the previous row
| code | datetime | total_turnover | volume | prev_total_turnover | prev_volume |
|---|---|---|---|---|---|
| 000001.XSHE | 2024-03-18 11:28:30 | 1084539136 | 102847384 | 1084484224 | 102842184 |
| 000001.XSHE | 2024-03-18 11:28:27 | 1084484224 | 102842184 | 1084468480 | 102840688 |
| 000001.XSHE | 2024-03-18 11:28:24 | 1084468480 | 102840688 | 1084468480 | 102840688 |
| 000001.XSHE | 2024-03-18 11:28:21 | 1084217856 | 102816888 | 1084198784 | 102815088 |
| 000001.XSHE | 2024-03-18 11:28:18 | 1084198784 | 102815088 | 1084198784 | 102815088 |
| 000001.XSHE | 2024-03-18 11:28:15 | 1084098816 | 102805584 | 1084023936 | 102798488 |
| 000001.XSHE | 2024-03-18 11:28:12 | 1084023936 | 102798488 | 1084010240 | 102797184 |
| 000001.XSHE | 2024-03-18 11:28:09 | 1084010240 | 102797184 | 1084010240 | 102797184 |
I want it to work fine in materialized views