mysql - 如何通过每天和每周定义的层次结构汇总事件类型(使用 SQL 或 ETL)

Question

如果我有以下事件表，并且我想对其进行总结，以便对于每个 ID、EventDay、EventWeek，我得到 1 行，例如 EventStatus 被报告为最严重/相关的 (High>Low>Normal) 。该解决方案需要应对更多的事件周和事件日

Is that something that can be accomplished with example MySQL or MariaDB compatible SQL or would ETL processes be better suited to the task (with outline of steps)?

示例表

ID  EventStatus EventDay    EventWeek
1   Normal      1           1
1   Low         2           1
1   High        2           1
1   Normal      2           1
2   Normal      1           1
2   Normal      2           1
3   Normal      2           1
3   Low         2           1

所需输出

ID  EventStatus EventDay    EventWeek
1   Normal      1           1
1   High        2           1
2   Normal      1           1
2   Normal      2           1
3   Low         2           1

除了 EventDay 和 EventWeek 之外，该解决方案是否还可以处理额外的列来约束事件 (EventLocation)

Answer 1

这是一个想法。 (id,eventweek,eventday) 上的索引会有所帮助。如果您可以将优先级 (H,L,N) 存储为整数(例如分别为 3,2,1)，那么您也可以将其添加到索引中，这将进一步提高性能 - 尽管可能还有其他方法...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(ID  INT NOT NULL
,EventStatus VARCHAR(12) NOT NULL
,EventDay INT NOT NULL
,EventWeek INT NOT NULL
,PRIMARY KEY(ID,EventWeek,EventDay,EventStatus)
);

INSERT INTO my_table VALUES
(1,'Normal',1,1),
(1,'Low',2,1),
(1,'High',2,1),
(1,'Normal',2,1),
(2,'Normal',1,1),
(2,'Normal',2,1),
(3,'Normal',2,1),
(3,'Low',2,1);

SELECT a.* 
  FROM my_table a
  JOIN
     (
       SELECT id
            , MAX(CASE eventstatus WHEN 'High' THEN 3 WHEN 'Low' THEN 2 ELSE 1 END) eventstatus
            , eventday
            , eventweek 
         FROM my_table 
        GROUP 
           BY id
            , eventweek
            , eventday
     ) b
    ON b.id = a.id 
   AND b.eventstatus = CASE a.eventstatus WHEN 'High' THEN 3 WHEN 'Low' THEN 2 ELSE 1 END
   AND b.eventweek = a.eventweek 
   AND b.eventday = a.eventday;

+----+-------------+----------+-----------+
| ID | EventStatus | EventDay | EventWeek |
+----+-------------+----------+-----------+
|  1 | Normal      |        1 |         1 |
|  1 | High        |        2 |         1 |
|  2 | Normal      |        1 |         1 |
|  2 | Normal      |        2 |         1 |
|  3 | Low         |        2 |         1 |
+----+-------------+----------+-----------+