时间戳

首页 > 解决方案 > 用于在括号开始和结束组中分组日期的Sql查询

mysql - 用于在括号开始和结束组中分组日期的Sql查询

问题描述

我在下表中有数据

+------------+----------+------------+
| Event name |Date      |Action      |
+------------+----------+------------+
| Event A    |10/08/2018| Started    | 
| Event B    |10/08/2018| Started    | 
| Event A    |11/08/2018| Ended      | 
| Event B    |12/08/2018| Ended      | 
| Event A    |13/08/2018| Started    | 
| Event A    |14/08/2018| Ended      | 
+------------+----------+------------+

我正在尝试编写一个查询，我想在其中列出事件正在进行的所有日期。

    +------------+----------+------------+
    | Event name |Date      |Status      |
    +------------+----------+------------+
    | Event A    |10/08/2018| Ongoing    | 
    | Event A    |11/08/2018| Ongoing    | 
    | Event A    |12/08/2018| null       | 
    | Event A    |13/08/2018| Ongoing    | 
    | Event A    |14/08/2018| Ongoing    | 
    | Event A    |15/08/2018| null       | 
    | Event B    |10/08/2018| Ongoing    | 
    | Event B    |11/08/2018| Ongoing    | 
    | Event B    |12/08/2018| Ongoing    | 
    | Event B    |13/08/2018| null       | 
    | Event B    |14/08/2018| null       | 
    | Event B    |15/08/2018| null       | 
    +------------+----------+------------+

我能够通过最小的开始和最大的结束找到一个连续的范围，但需要帮助如何将其分解为范围。

标签： mysqlsql

解决方案

您可以CROSS JOIN在日期表中使用，然后LEFT JOIN基于它。

如果您的 mysql 不支持窗口功能，您可以尝试使用 select subquery make row number byEventname并Action让结果生成开始日期到结束日期表。

CREATE TABLE T(
    Eventname varchar(50),
    Date date,
    Action varchar(50)
);

INSERT INTO T VALUES ('Event A' ,'2018/08/10','Started'); 
INSERT INTO T VALUES ('Event A' ,'2018/08/11','Ended'); 
INSERT INTO T VALUES ('Event B' ,'2018/08/10','Started'); 
INSERT INTO T VALUES ('Event B' ,'2018/08/12','Ended'); 
INSERT INTO T VALUES ('Event A' ,'2018/08/13','Started'); 
INSERT INTO T VALUES ('Event A' ,'2018/08/14','Ended'); 


create table cT(
  Date date
);


INSERT INTO cT VALUES ('2018/08/10'); 
INSERT INTO cT VALUES ('2018/08/11'); 
INSERT INTO cT VALUES ('2018/08/12'); 
INSERT INTO cT VALUES ('2018/08/13'); 
INSERT INTO cT VALUES ('2018/08/14'); 
INSERT INTO cT VALUES ('2018/08/15');

查询 1：

SELECT 
  t1.Eventname,
  ct.Date,
  CASE WHEN t2.Action IS NOT NULL THEN 'Oppening' ELSE NULL END dt
FROM 
(SELECT DISTINCT Eventname FROM T ) t1 CROSS JOIN CT ct
LEFT JOIN (
  SELECT t1.Eventname,
         t1.date 'startdate', 
         t2.date 'enddate',
         t1.Action
  FROM (
     SELECT *,(SELECT COUNT(*) 
              FROM T tt 
              where tt.date <= t1.date and tt.Eventname = t1.Eventname and tt.Action = t1.Action) rn 
    FROM T t1 
    where Action = 'Started'
  ) t1 INNER JOIN 
  (
    SELECT *,(SELECT COUNT(*) 
              FROM T tt 
              where tt.date <= t1.date and tt.Eventname = t1.Eventname and tt.Action = t1.Action) rn 
    FROM T t1 
    where Action = 'Ended'
  ) t2 on t1.rn = t2.rn and t1.Eventname = t2.Eventname
) t2 on ct.Date BETWEEN t2.startdate AND t2.enddate and t1.Eventname = t2.Eventname
WHERE ct.Date between '2018/08/10' and '2018/08/15'
ORDER BY t1.Eventname,ct.Date

结果：

| Eventname |       Date |       dt |
|-----------|------------|----------|
|   Event A | 2018-08-10 | Oppening |
|   Event A | 2018-08-11 | Oppening |
|   Event A | 2018-08-12 |   (null) |
|   Event A | 2018-08-13 | Oppening |
|   Event A | 2018-08-14 | Oppening |
|   Event A | 2018-08-15 |   (null) |
|   Event B | 2018-08-10 | Oppening |
|   Event B | 2018-08-11 | Oppening |
|   Event B | 2018-08-12 | Oppening |
|   Event B | 2018-08-13 |   (null) |
|   Event B | 2018-08-14 |   (null) |
|   Event B | 2018-08-15 |   (null) |

如果支持窗口功能，您可以使用row_number它来制作它。

SELECT 
  t1.Eventname,
  ct.Date,
  CASE WHEN t2.Action IS NOT NULL THEN 'Oppening' ELSE NULL END dt
FROM 
(SELECT DISTINCT Eventname FROM T ) t1 CROSS JOIN  cT ct
LEFT JOIN (
  SELECT t1.Eventname,
         t1.date 'startdate', 
         t2.date 'enddate',
         t1.Action
  FROM (
     SELECT *,ROW_NUMBER() OVER (PARTITION BY Eventname,Action ORDER BY date) rn 
    FROM T t1 
    where Action = 'Started'
  ) t1 INNER JOIN 
  (
    SELECT *,ROW_NUMBER() OVER (PARTITION BY Eventname,Action ORDER BY date) rn 
    FROM T t1 
    where Action = 'Ended'
  ) t2 on t1.rn = t2.rn and t1.Eventname = t2.Eventname
) t2 on ct.Date BETWEEN t2.startdate AND t2.enddate and t1.Eventname = t2.Eventname
WHERE ct.Date between '2018/08/10' and '2018/08/15'
ORDER BY t1.Eventname,ct.Date

sqlfiddle

推荐阅读