others - sql - 获取每天活动用户的数量

94 1


+------+------------+------------+


| City | Start_Date | End_Date |


+------+------------+------------+


| LA | 2019-06-01 | 2019-06-03 |


| LA | 2019-06-07 | 2019-06-10 |


| LA | 2019-06-09 | 2019-06-11 |


| LA | 2019-06-13 | 2019-06-14 |


| LO | 2019-06-01 | 2019-06-05 |


| LO | 2019-06-04 | 2019-06-05 |


| LO | 2019-06-07 | 2019-06-09 |


| LO | 2019-06-08 | 2019-06-09 |


+------+------------+------------+



我希望统计每一天的活跃用户数量

输出应该有点类似于


+------+------------+-------+


| City | Day | Count |


+------+------------+-------+


| LA | 2019-06-01 | 1 |


| LA | 2019-06-02 | 1 |


| LA | 2019-06-03 | 1 |


| LA | 2019-06-04 | 0 |


| LA | 2019-06-05 | 0 |


| LA | 2019-06-06 | 0 |


| LA | 2019-06-07 | 1 |


| LA | 2019-06-08 | 1 |


| LA | 2019-06-09 | 2 |


| LA | 2019-06-10 | 2 |


| LA | 2019-06-11 | 1 |


| LA | 2019-06-12 | 0 |


| LA | 2019-06-13 | 1 |


| LA | 2019-06-14 | 1 |


| LA | 2019-06-15 | 0 |


+------+------------+-------+



我所显示的输出只对于城市LA,我也希望在表中每个城市都有相似的输出。

我的输出说明

  • 在City =LA上有1个用户有活动订阅,类似于Day =2019-06-02 ,
  • Day =2019-06-09上的City =LA中有2个用户有活动订阅等等。

将感激任何帮助

SQL Fiddle Link

时间: 原作者:

54 2

这样应该可以:


select city, '2019-06-11', count(*)


from table1


where end_date < '2019-06-11'


group by 1




你应该用一个变量替换日期,并放入你需要的日期。

原作者:
150 3

尝试下面的查询,测试数据:


DECLARE @tbl TABLE (City varchar(5),Start_Date date,End_Date date);


INSERT INTO @tbl values


( 'LA','2019-06-01','2019-06-03' ),


( 'LA','2019-06-07','2019-06-10' ),


( 'LA','2019-06-09','2019-06-11' ),


( 'LA','2019-06-13','2019-06-14' ),


( 'LO','2019-06-01','2019-06-05' ),


( 'LO','2019-06-04','2019-06-05' ),


( 'LO','2019-06-07','2019-06-09' ),


( 'LO','2019-06-08','2019-06-09' );



使用CTE作为日历函数的实际查询:


DECLARE @start DATE, @end DATE;


SELECT @start = MIN(Start_Date), @end = MAX(End_Date) FROM @tbl;



;WITH cte AS (


 SELECT @start dt


 UNION ALL


 SELECT DATEADD(day, 1, dt) FROM CTE


 WHERE dt < @end


)



SELECT cte.dt, SUM(CASE WHEN t.City IS NULL THEN 0 ELSE 1 END) FROM cte


LEFT JOIN @tbl t 


ON cte.dt BETWEEN t.Start_Date AND t.End_Date AND t.City = 'LA'


GROUP BY cte.dt



原作者:
92 1

你需要一系列的日期,

然后使用CROSS JOIN分配行,一个LEFT JOIN以引入所需的值,然后使用GROUP BY获取计数:


with dates as (


 select convert(date, '2019-06-01') as dte


 union all


 select dateadd(day, 1, dte) as dte


 from dates


 where dte < '2019-06-15'


 )


select c.city, d.dte, count(t.city)


from (select distinct city from t) c cross join


 dates d left join


 t


 on t.city = c.city and t.start_date <= d.dte and d.end_date >= t.dte 


group by c.city, d.dte


order by c.city, d.dte;



这个是使用SQL Server的db < > -- 。

原作者:
...