过滤SQL查询

Question

我正在努力用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

我已经尝试添加下面的连接来显示具有最大日期的记录。但是，另外三个列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  

如有任何帮助，不胜感激

Answer 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