如何从MySQL5.5中删除重复记录派生表,按日期聚合数据

alen0pnh  于 2021-06-19  发布在  Mysql
关注(0)|答案(1)|浏览(265)

下表名为hb\u lead,它存储唯一的lead、代码和时间戳。我们把这里记录的事件称为

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\u-lead\u-engagement表存储了很多次,每个lead都与一些东西啮合。让我们把在这里登记的事件称为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,我可以与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

使用一个派生表,首先合并两个表以列出所有事件日期,然后计数事件,我创建了这样一个表,当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

所以我知道解决这个问题的方法,比如说每一行的聚合数据是连接两个表,主要是左连接,然后是用例。但是,由于这是一个派生表,它可以与自身联接,因此我的问题是,在知道派生表不能与自身联接的情况下,如何聚合生成的派生表。
如果想知道我是如何得出日期的,我的问题是:

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,所以带as不在讨论范围之内。谢谢。

am46iovg

am46iovg1#

你可以试着 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     |

db fiddle视图

相关问题