时间戳

首页 > 解决方案 > 根据日期获取价值

sql - 根据日期获取价值

问题描述

我试图根据未来的定价来获得产品的价格点。由于价格点不同，我不能真正做到 Max（到期日期）。而且我也不能做 Max(Price)，因为高价可能是过期的，也可能是新的。我的数据看起来像这样：

供应商 产品 价格 生效日期 截止日期
供应商 1 一个 800 04-01-2121 2023 年 12 月 31 日
供应商 1 一个 1000 01-01-2121 03-31-2023
供应商 1 乙 500 04-01-2121 2023 年 12 月 31 日
供应商 1 乙 400 01-01-2121 03-31-2023
供应商 2 D 200 01-01-2121 2023 年 12 月 31 日
供应商 2 C 600 01-01-2121 2023 年 12 月 31 日

我试图得到的结果如下：

供应商 产品 价格 生效日期 截止日期
供应商 1 一个 800 04-01-2121 2023 年 12 月 31 日
供应商 1 乙 500 04-01-2121 2023 年 12 月 31 日
供应商 2 D 200 01-01-2121 2023 年 12 月 31 日
供应商 2 C 600 01-01-2121 2023 年 12 月 31 日

有任何想法吗？

标签： sqlgoogle-bigquery

解决方案

要获得expirationdate最大的产品信息，我们可以按产品的降序对行进行分组，expirationdate并找到每个产品的序列号最低的行，或者我们可以简单地使用子查询来选择产品组中 where expirationdate=的信息。max(expirationdate)第一种方法会更有效。但是，如果您的 dbms 不支持 row_number() 那么您可以使用第二种方法。

架构：

create table mydata(Supplier varchar(30),Product varchar(30), Price int,EffectiveDate date,   ExpirationDate date);
insert into mydata values('Supplier 1',   'A',    800 ,'04-01-2121',  '12-31-2023');
insert into mydata values('Supplier 1',   'A',    1000    ,'01-01-2121',  '03-31-2023');
insert into mydata values('Supplier 1',   'B',    500 ,'04-01-2121',  '12-31-2023');
insert into mydata values('Supplier 1',   'B',    400 ,'01-01-2121',  '03-31-2023');
insert into mydata values('Supplier 2',   'D',    200 ,'01-01-2121',  '12-31-2023');
insert into mydata values('Supplier 2',   'C',    600 ,'01-01-2121',  '12-31-2023');

查询#1

WITH cte
 AS (SELECT supplier,
            product,
            price,
            effectivedate,
            expirationdate,
            ROW_NUMBER ()
                OVER (PARTITION BY product
                          ORDER BY expirationdate DESC)
                rn
       FROM mydata)
SELECT supplier,
       product,
       price,
       effectivedate,
       expirationdate
  FROM cte
 WHERE rn = 1

输出：

供应商 产品 价格 生效日期 截止日期
供应商 1 一个 800 2121-04-01 2023-12-31
供应商 1 乙 500 2121-04-01 2023-12-31
供应商 2 C 600 2121-01-01 2023-12-31
供应商 2 D 200 2121-01-01 2023-12-31

旧版本 DBMS 的查询#2：

SELECT supplier,
       product,
       price,
       effectivedate,
       expirationdate
  FROM mydata m
 where effectivedate = 
       (select max(effectivedate) from mydata md where m.product=md.product)

输出：

供应商 产品 价格 生效日期 截止日期
供应商 2 D 200 2121-01-01 2023-12-31
供应商 2 C 600 2121-01-01 2023-12-31
供应商 1 乙 500 2121-04-01 2023-12-31
供应商 1 一个 800 2121-04-01 2023-12-31

db<>在这里摆弄

推荐阅读