sql - 过滤 SQL 查询

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

相似文章

最新文章