Hive SQL 题求教

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

这是一个创建于 717 天前的主题，其中的信息可能已经有所发展或是发生改变。

已知价格会随着时间发生变化，想要求得每款产品的每个价格的起始时间,示例数据如下。需要注意的是当价格发生变动后又恢复了原价的话，起始日期需重新计算，如 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

2022-12-06 22:39:34 +08:00

可以搜一下 [SQL 连续签到天数] 的题，看起来差不多的思路。

liprais

2022-12-06 23:11:51 +08:00 via iPhone

按价格分组之后连续的日期有啥特征?
连续的日期减去一个自增的数列是一个定值，按这个分组不就完了

wloverine

2022-12-06 23:30:51 +08:00

@liprais 没明白你的意思

wloverine

2022-12-06 23:31:59 +08:00

@saluton 谢谢，有点连续签到的意思，但还是有些差异的

tinywhale

2022-12-07 07:56:32 +08:00

这个是典型的 gaps and islands 类型问题，技巧是要先分组，然后每个组的 min/max date 就是开始和结束日期。

jiangwei2222

2022-12-07 08:24:33 +08:00 via Android

select price,max (dt),min (dt) from xxx group price

cau1iflower

2022-12-07 08:46:23 +08:00

ChatGPT 了解一下

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

2022-12-07 09:59:42 +08:00

@jiangwei2222 直接 group by 的话就会造成 item_id 为 2 ，price 为 399 的起始日期变成了 2020-01-01~2020-01-05 ，这显然是不合理的

wxf666

2022-12-07 11:02:49 +08:00

@cau1iflower 有啥数据库支持直接引用别名的吗？

比如 `LAG(start_date)`、`LEAD(start_date)` 这些

SbloodyS

2022-12-07 11:12:40 +08:00

@wxf666 比如 Clickhouse 支持直接引用别名

wxf666

2022-12-07 13:53:34 +08:00

@SbloodyS 数据库新手问下，现在基本都不用 PostgreSQL 、MySQL 、SQL Server 、Oracle 、SQLite 这些数据库了是吗？

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

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

2022-12-09 13:42:04 +08:00

@wxf666 有语法限制的，AS 后面的别名和 AS 前面的字段别名不能一致，这样来避免冲突

wxf666

2022-12-09 14:53:30 +08:00

@SbloodyS 是说，没法在 `... AS a` 内使用 `a`，是吧？

但可以 `... AS a, (a + 1) AS b`？

SbloodyS

2022-12-09 15:46:23 +08:00

@wxf666 是的

wxf666

2022-12-09 16:03:22 +08:00

@SbloodyS 那 8 楼的 SQL ，应该没有哪款数据库能运行了。因为：

```sql
SELECT ... LAG(start_date) ... AS start_date
...
```