时间戳

首页 > 解决方案 > 在特定事件之前计数事件

mysql - 在特定事件之前计数事件

问题描述

假设，我有一个包含以下列的表格：

date | event     | user_id |  unit_id  |cost |  ad_id  | spend
03-15 | impression | 2353   |   3436 | 0.15 |  NULL | NULL
03-15 | impression | 2353   |   3436 | 0.12 |  NULL | NULL
03-15 | impression | 1234   |   5678 | 0.10 |  NULL | NULL
03-15 | click | 1234   |   5678 |  NULL  |  NULL | NULL
03-15 | create_ad |     1234 | 5678 | NULL | 6789 | 10

我想计算在用户创建 ID 之前平均需要多少次展示。在此特定场景中，用户 1234 需要一次印象来创建广告。

我不确定我能否以某种方式使用日期来区分事件（但从逻辑上讲，所有这些事件都应该在不同的时刻发生）。但是，您可以看到展示次数在ad_id和支出中具有 NULL，而 create_id 在支出中确实有一个数字。

这个不行：

select i.user_id
     , i.unit_id
     , count(i.event) impressions_n
     , count(c.event) as ads_n
  from add4ad i
   left 
  join add4ad c
     on i.user_id = c.user_id 
   and i.unit_id = c.unit_id
 where i.event in ('impression')
   and c.spend <> NULL
 group 
    by i.user_id
     , i.unit_id

我用这些数据创建了一个SQLFiddle

标签： mysqlsqljoinleft-joinself-join

解决方案

我去了 SQL Fiddle 并通过 MS SQL 引擎运行了测试。

CREATE TABLE add4ad (date date, event varchar(10), user_id int,
                   unit_id int, cost float, ad_id float, spend float);
INSERT INTO add4ad (date, Event, user_id,unit_id,cost,ad_id,spend)
VALUES
    ('2018-03-15','impression','2353','3436','0.15',NULL,NULL),
    ('2018-03-15','impression','2353','3436','0.12',NULL,NULL),
    ('2018-03-15','impression','2353','3436','0.10',NULL,NULL),
    ('2018-03-15','click','1234','5678', NULL, NULL,NULL),
    ('2018-03-15','create_ad','2353','5678', NULL, 6789,10);

我的查询

with e10 as (select  user_id, event, date, rowid=row_number() over (Partition by user_id order by date)
from add4ad
where event='create_ad'
),
e20 as ( -- get the first create_ad event
select user_id, date
  from e10
  where rowid=1
  )
  select a.user_id, count(1) as N
  from e20 inner join add4ad a
  on e20.user_id=a.user_id
  and a.date<=e20.date
  and a.event='impression'
  group by a.user_id

推荐阅读