我正在努力使用 SQL 查询以显示与先前价格和不同价格的最新价格。我有下面的代码，它显示了使用 LAG 的先前价格的价格。
SELECT
t.site_id As "Site",
t.type As "Product",
t.recorded_time As "Recorded Date",
t.price / 10 As "Price",
LAG(t.price) OVER(PARTITION BY t.site_id , t.type ORDER BY t.recorded_time ) / 10 previous_price,
LAG(t.recorded_time) OVER(PARTITION BY t.site_id , t.type ORDER BY t.recorded_time ) previous_date,
(t.price -LAG(t.price) OVER(PARTITION BY t.site_id , t.type ORDER BY t.recorded_time )) / 10 As "Pricing change"
FROM table t我已尝试添加以下联接以仅显示具有最大 date 的记录。但是，previous_price、previous_date 和 Price change 这三个附加列都是空的。
INNER JOIN( SELECT
site_id,
max(recorded_time) as MaxDate,
type
FROM table
GROUP BY site_id,
type
) tm ON t.site_id = tm.site_id
AND t.recorded_time = tm.MaxDate
AND t.type = tm.type任何帮助将不胜感激
回答1
我认为您想将原始查询放在子查询中，然后将该子查询连接到 tm 子查询，因此 LAG 将在连接之前而不是之后计算，如下所示：
SELECT
t.*
FROM (
SELECT
t.site_id,
t.type,
t.recorded_time,
t.price / 10 As price,
LAG(t.price) OVER (PARTITION BY t.site_id, t.type ORDER BY t.recorded_time) / 10 previous_price,
LAG(t.recorded_time) OVER (PARTITION BY t.site_id, t.type ORDER BY t.recorded_time) previous_date,
(t.price - LAG(t.price) OVER (PARTITION BY t.site_id,t.type ORDER BY t.recorded_time)) / 10 As pricing_change
FROM
table
) t
INNER JOIN (
SELECT
site_id,
max(recorded_time) as MaxDate,
type
FROM
table
GROUP BY
site_id,
type
) tm
ON t.site_id = tm.site_id
AND t.recorded_time = tm.MaxDate
AND t.type = tm.type