mysql - SQL 中分区的字符串连接？

Question

我想在排名到新列(group by 似乎不起作用)后连接周围的行(在以下示例中只有周围的 2 行)，这是我拥有的数据:

架构(MySQL v8.0)

CREATE TABLE log_table (
  `user_id` VARCHAR(5),
  `date_time` DATETIME,
  `event_name` VARCHAR(10),
  `trivial` int
);

INSERT INTO log_table
  (`user_id`, `date_time`, `event_name`, `trivial`)
VALUES
  ('001', '2020-12-10 10:00:02', 'c', 3),
  ('001', '2020-12-10 10:00:01', 'b', 9),
  ('001', '2020-12-10 10:00:40', 'e', 2),
  ('001', '2020-12-10 10:00:20', 'd', 6),
  ('001', '2020-12-10 10:00:00', 'a', 1),
  ('002', '2020-12-10 10:00:10', 'C', 9),
  ('002', '2020-12-10 10:00:50', 'D', 0),
  ('002', '2020-12-10 10:00:02', 'A', 2),
  ('002', '2020-12-10 10:00:09', 'B', 4);

---

为了说明我想做什么。我可以使用 sum 对数值求和子句如下:
查询 #1

SELECT *,
       SUM(trivial)
         over(
           PARTITION BY user_id
           ORDER BY user_id, date_time ROWS BETWEEN 2 preceding AND 2 following)
       AS
       trivial_new
FROM   log_table; 

<表类="s-表"><头>user_id日期_时间事件名称琐碎的trivial_new<正文>0012020-12-10 10:00:00一个1130012020-12-10 10:00:01b9190012020-12-10 10:00:02c3210012020-12-10 10:00:20d6200012020-12-10 10:00:40e2110022020-12-10 10:00:02一个2150022020-12-10 10:00:09B4150022020-12-10 10:00:10C9150022020-12-10 10:00:50D013

---

View on DB Fiddle

对于字符串字段event_name ，我试过这个片段:
查询#2

SELECT *,
       Concat(event_name)
         over(
           PARTITION BY user_id
           ORDER BY user_id, date_time ROWS BETWEEN 2 preceding AND 2 following)
       AS
       event_name_new
FROM   log_table

这是我的预期结果:

<表类="s-表"><头>user_id日期_时间事件名称琐碎的event_name_new<正文>0012020-12-10 10:00:00一个1abc0012020-12-10 10:00:01b9abcd0012020-12-10 10:00:02c3abcde0012020-12-10 10:00:20d6bcde0012020-12-10 10:00:40e2cde0022020-12-10 10:00:02一个2ABC0022020-12-10 10:00:09B4ABCD0022020-12-10 10:00:10C9ABCD0022020-12-10 10:00:50D0BCD

但是查询 #2 不能把我带到这里，我用谷歌搜索了但我能找到的是关于 group by 的信息(引用 this 和 this 和 this)。

我知道我可以使用 LAG 解决这个问题和 LEAD (对于以下行)但我需要连接新列，当我需要连接许多行时，我需要做很多手动工作，比如用 , 等分隔符连接它们等等

我可以在不使用 LAG 的情况下一步完成吗？和 LEAD ？

Answer 1

您可以使用 CTE 解决此问题:首先计算每个 user_id 的行号，按 date_time 排序；然后根据行号在该行的最小/最大行号范围内(从行号 - 之前到行号 + 之后)将表连接到自身。然后你可以从 JOINed 表中 GROUP_CONCAT event_name 字段:

SET @before := 2;
SET @after := 2;

WITH rns AS (
  SELECT *, 
         CAST(ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date_time) AS SIGNED) AS rn
  FROM log_table
)
SELECT r1.user_id, r1.date_time, r1.event_name,
       GROUP_CONCAT(r2.event_name SEPARATOR '') AS event_name_new
FROM rns r1
JOIN rns r2 ON r2.user_id = r1.user_id
           AND r2.rn BETWEEN r1.rn - @before AND r1.rn + @after
GROUP BY r1.user_id, r1.date_time, r1.event_name
ORDER BY r1.user_id, r1.rn

输出(前 2 个和后 2 个):

user_id     date_time               event_name  event_name_new
001         2020-12-10 10:00:00     a           abc
001         2020-12-10 10:00:01     b           abcd
001         2020-12-10 10:00:02     c           abcde
001         2020-12-10 10:00:20     d           bcde
001         2020-12-10 10:00:40     e           cde
002         2020-12-10 10:00:02     A           ABC
002         2020-12-10 10:00:09     B           ABCD
002         2020-12-10 10:00:10     C           ABCD
002         2020-12-10 10:00:50     D           BCD

Demo on db-fiddle