V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
wloverine
V2EX  ›  程序员

Hive SQL 题求教

  •  
  •   wloverine · 2022-12-06 21:55:06 +08:00 · 1331 次点击
    这是一个创建于 529 天前的主题,其中的信息可能已经有所发展或是发生改变。

    已知价格会随着时间发生变化,想要求得每款产品的每个价格的起始时间,示例数据如下。 需要注意的是当价格发生变动后又恢复了原价的话,起始日期需重新计算,如 item_id 为 2 ,price 为 399 的数据,就产出了两条起始时间。只能编写 SQL 实现

    input:
    item_id price dt
    1 100 2020-01-01
    1 100 2020-01-02
    1 120 2020-01-03
    2 399 2020-01-01
    2 399 2020-01-02
    2 399 2020-01-03
    2 499 2020-01-04
    2 399 2020-01-05
    
    output:
    item_id price start_date end_date
    1       100     2020-01-01      2020-01-02
    1       120     2020-01-03      2020-01-03
    2       399     2020-01-01      2020-01-03
    2       499     2020-01-04      2020-01-04
    2       399     2020-01-05      9999-12-31
    
    第 1 条附言  ·  2022-12-07 10:33:01 +08:00

    感谢大佬们的提示,我把我写的sql贴出来,有问题还请指正。

    select item_id,price,start_date,case when start_date!=end_date then end_date else '9999-12-31' end as end_date
    from
    (
     select item_id,price,min(dt) start_date,max(dt) end_date
     from
     (
      select 
      item_id,
      price,
      dt,
      row_number() over(partition by item_id,price order by dt) rk,
      date_sub(dt,row_number() over(partition by item_id,price order by dt)) res
      from price_info
     )a
     group by item_id,price,res
    )b
    
    18 条回复    2022-12-09 16:03:22 +08:00
    saluton
        1
    saluton  
       2022-12-06 22:39:34 +08:00
    可以搜一下 [SQL 连续签到天数] 的题,看起来差不多的思路。
    liprais
        2
    liprais  
       2022-12-06 23:11:51 +08:00 via iPhone
    按价格分组之后连续的日期有啥特征?
    连续的日期减去一个自增的数列是一个定值,按这个分组不就完了
    wloverine
        3
    wloverine  
    OP
       2022-12-06 23:30:51 +08:00
    @liprais 没明白你的意思
    wloverine
        4
    wloverine  
    OP
       2022-12-06 23:31:59 +08:00
    @saluton 谢谢,有点连续签到的意思,但还是有些差异的
    tinywhale
        5
    tinywhale  
       2022-12-07 07:56:32 +08:00
    这个是典型的 gaps and islands 类型问题,技巧是要先分组,然后每个组的 min/max date 就是开始和结束日期。
    jiangwei2222
        6
    jiangwei2222  
       2022-12-07 08:24:33 +08:00 via Android
    select price,max (dt),min (dt) from xxx group price
    cau1iflower
        7
    cau1iflower  
       2022-12-07 08:46:23 +08:00
    ChatGPT 了解一下
    cau1iflower
        8
    cau1iflower  
       2022-12-07 08:48:53 +08:00
    @cau1iflower
    ```
    SELECT item_id, price,
    -- 如果当前价格和前一个价格不同,那么这个价格的起始时间就是当前日期。
    -- 否则,起始时间就是前一个价格的起始时间。
    CASE WHEN price != LAG(price) OVER (PARTITION BY item_id ORDER BY date)
    THEN date
    ELSE LAG(start_date) OVER (PARTITION BY item_id ORDER BY date)
    END AS start_date,
    -- 如果当前价格和后一个价格不同,那么这个价格的结束时间就是后一个价格的起始时间。
    -- 否则,这个价格的结束时间就是 NULL 。
    CASE WHEN price != LEAD(price) OVER (PARTITION BY item_id ORDER BY date)
    THEN LEAD(start_date) OVER (PARTITION BY item_id ORDER BY date)
    ELSE NULL
    END AS end_date
    FROM prices

    ```
    wloverine
        9
    wloverine  
    OP
       2022-12-07 09:59:42 +08:00
    @jiangwei2222 直接 group by 的话就会造成 item_id 为 2 ,price 为 399 的起始日期变成了 2020-01-01~2020-01-05 ,这显然是不合理的
    wxf666
        10
    wxf666  
       2022-12-07 11:02:49 +08:00
    @cau1iflower 有啥数据库支持直接引用别名的吗?

    比如 `LAG(start_date)`、`LEAD(start_date)` 这些
    SbloodyS
        11
    SbloodyS  
       2022-12-07 11:12:40 +08:00
    @wxf666 比如 Clickhouse 支持直接引用别名
    wxf666
        12
    wxf666  
       2022-12-07 13:53:34 +08:00
    @SbloodyS 数据库新手问下,现在基本都不用 PostgreSQL 、MySQL 、SQL Server 、Oracle 、SQLite 这些数据库了是吗?
    blue7wings
        13
    blue7wings  
       2022-12-07 14:10:54 +08:00
    以下是 OpenAI 给出的答案,不知道对不对,你可以参考一下:)
    Here is one possible solution using SQL:

    ```sql
    SELECT item_id, price, MIN(dt) as start_date,
    (SELECT MIN(dt) FROM prices AS p2
    WHERE p2.item_id = p1.item_id AND p2.price != p1.price AND p2.dt > p1.dt)
    as end_date
    FROM prices as p1
    GROUP BY item_id, price
    ```

    This query groups the prices for each item_id and price combination and finds the minimum dt (the start date) for each group. Then, for each group, it uses a subquery to find the minimum dt for the next price change (the end date) for the same item_id. If there is no next price change, the end date is set to the maximum date value.

    You can use this query as a starting point and modify it to fit your specific needs.
    wxf666
        14
    wxf666  
       2022-12-09 13:25:23 +08:00
    @SbloodyS 突然很好奇,如果 Clickhouse 支持直接用别名的话,下面这种 SQL 会计算成什么?

    ```sql
    SELECT IF(id % 2, LEAD(a) OVER (...), LAG(a) OVER (...)) AS a
    FROM ...
    ```

    大意:若 id 为奇数,则取下一行的值,否则取上一行的值
    SbloodyS
        15
    SbloodyS  
       2022-12-09 13:42:04 +08:00
    @wxf666 有语法限制的,AS 后面的别名和 AS 前面的字段别名不能一致,这样来避免冲突
    wxf666
        16
    wxf666  
       2022-12-09 14:53:30 +08:00
    @SbloodyS 是说,没法在 `... AS a` 内使用 `a`,是吧?

    但可以 `... AS a, (a + 1) AS b`?
    SbloodyS
        17
    SbloodyS  
       2022-12-09 15:46:23 +08:00
    @wxf666 是的
    wxf666
        18
    wxf666  
       2022-12-09 16:03:22 +08:00
    @SbloodyS 那 8 楼的 SQL ,应该没有哪款数据库能运行了。因为:

    ```sql
    SELECT ... LAG(start_date) ... AS start_date
    ...
    ```
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2433 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 26ms · UTC 13:58 · PVG 21:58 · LAX 06:58 · JFK 09:58
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.