mysql - 如何从 MySql 5.5 中删除重复记录。派生表，按日期聚合数据

Question

下表名为 hb_lead 存储唯一的线索、代码和时间戳。让我们将此处记录的事件称为 reg

 id_hb_lead | lead_code | creation_date
 -----------|-----------|--------------
  1         | ABC       | 2018-10-01
  2         | DEF       | 2018-10-02
  3         | GHI       | 2018-10-02
  4         | CYZ       | 2018-10-03
  5         | HIJ       | 2018-10-10

hb_lead_engagement 表存储了每个潜在客户多次参与某些事情的次数。让我们调用这里注册的事件enga

 id_hb_lead_engagement | id_b_lead |  id_event | event_date
 ----------------------|-----------|-----------|----------- 
  1                    | 1         |       12  | 2018-10-02
  2                    | 2         |       12  | 2018-10-03
  3                    | 3         |       12  | 2018-10-03
  4                    | 4         |       12  | 2018-10-06
  5                    | 3         |       12  | 2018-10-08
  6                    | 5         |       12  | 2018-10-16
  7                    | 2         |       12  | 2018-10-19
  8                    | 3         |       12  | 2018-10-10
  9                    | 5         |       12  | 2018-10-10

我需要生成一个这样的表，其中针对每个唯一日期计算 reg 和 enga 事件。请不要关心 0 或 null，我可以 COALESCE 与 0 或 null ...

 date      |  reg     | enga
-----------|----------|-----------
2018-10-01 |    1     |    
2018-10-02 |    2     |    1
2018-10-03 |    1     |    2
2018-10-06 |          |    1 
2018-10-08 |          |    1
2018-10-10 |    1     |    2
2018-10-16 |          |    1
2018-10-19 |          |    1

使用 Derived Table，首先 UNION 两个表列出所有事件日期，然后是 COUNT 事件，我创建了一个表这在 reg 和 enga 事件都大于 1 的同一日期时无法复制日期。

 date      |  regs          | engas
-----------|----------------|-----------
2018-10-01 |    1           |    
2018-10-02 |    2           |    
2018-10-02 |                |    1
2018-10-03 |    1           |    
2018-10-03 |                |    2
2018-10-06 |                |    1 
2018-10-08 |                |    1
2018-10-10 |    1           |     
2018-10-10 |                |    2
2018-10-16 |                |    1
2018-10-19 |                |    1

所以我知道解决这个问题的方法，比如每一行的聚合数据是JOIN两个表，主要是LEFT JOIN，然后使用CASE。然而，由于这是一个派生表，它可以与自身连接，所以我的问题是如何聚合生成的派生表，知道派生表不能与自身连接。

如果想知道我是如何得出日期的，这是我的查询:

select *
from
(
  select   date_format(l.fecha_creacion, '%Y-%m-%d') as categoria, 
  count(*) as reg, '' as enga
  from hb.hb_lead l    
  group by  categoria

union 

 select  date_format(le.le_fecha_creacion, '%Y-%m-%d') fecha, '', 
 count(*) 
 from hb.hb_lead_engagement le
 where le.id_hb_evento = 12
 group by   fecha
 order by 1
) t1

我仅限于 MySql 版本:5.5.5-10.1.36-MariaDB，因此 **With a as ** 不在讨论之列。谢谢。

Answer 1

您可以尝试UNION 日期列，然后基于它使用outer join。

**Schema (MySQL v5.5)**

create table hb_lead (
   id_hb_lead int,
   lead_code varchar(50),
   creation_date date
); 



insert into hb_lead values (1,'ABC', '2018-10-01');
insert into hb_lead values (2,'DEF', '2018-10-02');
insert into hb_lead values (3,'GHI', '2018-10-02');
insert into hb_lead values (4,'CYZ', '2018-10-03');
insert into hb_lead values (5,'HIJ', '2018-10-10');


create table hb_lead_engagement (
   id_hb_lead_engagement int,
   id_b_lead int,
   id_event int,
  event_date date
);



insert into hb_lead_engagement values (1,1,12, '2018-10-02');
insert into hb_lead_engagement values (2,2,12, '2018-10-03');
insert into hb_lead_engagement values (3,3,12, '2018-10-03');
insert into hb_lead_engagement values (4,4,12, '2018-10-06');
insert into hb_lead_engagement values (5,3,12, '2018-10-08');
insert into hb_lead_engagement values (6,5,12, '2018-10-16');
insert into hb_lead_engagement values (7,2,12, '2018-10-19');
insert into hb_lead_engagement values (8,3,12, '2018-10-10');
insert into hb_lead_engagement values (9,5,12, '2018-10-10');

---

查询#1

select dt,
    COUNT(id_hb_lead) regs,
    COUNT(id_hb_lead_engagement) engas
from 
(
  select event_date dt from hb_lead_engagement
  UNION 
  SELECT creation_date FROM hb_lead  
) t1 
LEFT JOIN hb_lead_engagement bhle on t1.dt = bhle.event_date
LEFT JOIN hb_lead bhl on t1.dt = bhl.creation_date
GROUP BY dt;

| dt         | regs | engas |
| ---------- | ---- | ----- |
| 2018-10-01 | 1    | 0     |
| 2018-10-02 | 2    | 2     |
| 2018-10-03 | 2    | 2     |
| 2018-10-06 | 0    | 1     |
| 2018-10-08 | 0    | 1     |
| 2018-10-10 | 2    | 2     |
| 2018-10-16 | 0    | 1     |
| 2018-10-19 | 0    | 1     |

---

View on DB Fiddle