时间戳

首页 > 解决方案 > 如何根据行日差和分区对 SQL 中的列进行排名？

mysql - 如何根据行日差和分区对 SQL 中的列进行排名？

问题描述

我正在尝试根据行差 < 3 在列上获取 RANK()。

select hotel.*,
IFNULL(datediff(visit_date, lag(visit_date)
OVER (partition by hotel_id)), 0) as diff
from hotel;

我得到以下输出，

hotel_id customer_id  visit_date  diff
1            1        2020-01-01    0
1            2        2020-01-03    2
2            1        2020-01-01    0
2            2        2020-01-10    9
2            3        2020-01-14    4
3            1        2020-01-04    0
3            1        2020-01-11    7

我坚持使用 RANK() 部分。

预期输出：如果 Day Difference 小于 3，则为 1，否则为 2。如果下一个大于 3 天，则为 3，依此类推

hotel_id customer_id  visit_date  rank
1            1        2020-01-01    1
1            2        2020-01-03    1
2            1        2020-01-01    1
2            2        2020-01-10    2
2            3        2020-01-14    3
3            1        2020-01-04    1
3            1        2020-01-11    2

标签： mysqlsqlmysql-workbench

解决方案

您可以使用此查询来生成您的rank值。它使用几个CTEs，第一个为每次访问生成行号（基于每个酒店），第二个（递归）CTE生成rank值，从第一个开始迭代行，仅在差异时CTE递增rank日期超过 2 天：

WITH RECURSIVE hotel_rows AS (
  SELECT hotel_id, customer_id, visit_date,
         ROW_NUMBER() OVER (PARTITION BY hotel_id ORDER BY visit_date) AS rn
  FROM hotel
  ORDER BY hotel_id, visit_date
),
ranks AS (
  SELECT hotel_id, customer_id, visit_date, rn, 1 AS `rank`
  FROM hotel_rows
  WHERE rn = 1
  UNION ALL
  SELECT h.hotel_id, h.customer_id, h.visit_date, h.rn,
         r.rank + (h.visit_date > r.visit_date + INTERVAL 2 DAY)
  FROM hotel_rows h
  JOIN ranks r ON h.hotel_id = r.hotel_id
              AND h.rn = r.rn + 1
)
SELECT SELECT hotel_id, customer_id, visit_date, `rank`
FROM ranks
ORDER BY hotel_id, visit_date

输出（对于我稍微扩展的演示）：

hotel_id    customer_id     visit_date  rank
1           1               2020-01-01  1
1           2               2020-01-03  1
2           1               2020-01-01  1
2           2               2020-01-10  2
2           3               2020-01-14  3
2           1               2020-01-15  3
2           2               2020-01-20  4
3           1               2020-01-04  1
3           1               2020-01-11  2

dbfiddle 上的演示

推荐阅读