WITH all_dates AS (
SELECT
date_trunc ('day', d) AS DAY
FROM
generate_series (
:startTs :: TIMESTAMP,
:endTs :: TIMESTAMP,
'1 day' :: INTERVAL
) d
),
subquery AS (
SELECT
date_trunc (
'day',
to_timestamp (ts / 1000)
) AS DAY,
COALESCE (
MAX(
CASE
WHEN ABS(dbl_yb__v) = max_abs_dbl_yb__v THEN
dbl_yb__v
END
),
0.00
) AS original_value
FROM
(
SELECT
ts,
dbl_yb__v,
MAX(ABS(dbl_yb__v)) OVER (
PARTITION BY date_trunc (
'day',
to_timestamp (ts / 1000)
)
) AS max_abs_dbl_yb__v
FROM
ts_kv_hour
WHERE
entity_id = : entityId
AND (
key_id = : keyId1
OR key_id = : keyId2
)
AND dbl_yb__v < 10000
AND dbl_yb__v > - 10000
) subquery_inner
GROUP BY
DAY
) SELECT
ad. DAY,
COALESCE (sq.original_value, 0.00) AS original_value
FROM
all_dates ad
LEFT JOIN subquery sq ON ad. DAY = sq. DAY
ORDER BY
ad.DAY
在thingsboard 平台,不执行也不报错