题解 | #每篇文章同一时刻最大在看人数#在第二组测试用例的时候出现了一个问题，少了数据

思路和大家的差不多，但是中间出了一个问题，标注一下，提醒一下自己下次注意。刚开始写的时候这样的

错误的sql

WITH t1 AS (
	SELECT artical_id,in_time dt,1 diff FROM tb_user_log WHERE artical_id <> 0	
	UNION
	SELECT artical_id,out_time dt,-1 diff FROM tb_user_log WHERE artical_id <> 0	
	),
	t2 as(
	SELECT artical_id, sum(diff) over(PARTITION BY artical_id ORDER BY dt,diff desc) sum_diff FROM t1
)
SELECT
	*
FROM
	t2

问题分析

开始的时候，编码+联立出来的中间表，只有16条记录，没有发现问题，最后算了一下，应该是有18条记录的，少了两条，检查了一下才发现问题，问题出在下面的三条数据

原来是我在t1表union的时候，就没有吧uid加入查询字段，所以导致我t1表，存在了artical_id 和 dt和diff相同，所以被合并成了一条数据，所以才少了数据。

解决方案

1. 在t1表的查询字段加上 uid就可以了
2. 或者把UNION 改成 UNION ALL

WITH t1 AS (
  
		SELECT uid,artical_id,in_time dt,1 diff FROM tb_user_log WHERE artical_id <> 0	
		UNION
		SELECT uid,artical_id,out_time dt,-1 diff FROM tb_user_log WHERE artical_id <> 0	
  
	),
    t2 as(
      
		SELECT 
		artical_id, 
    	sum(diff) over ( PARTITION BY artical_id ORDER BY dt ,diff desc) sum_diff
		FROM t1
)
SELECT
	artical_id,max(sum_diff) max_uv
FROM
	t2
GROUP BY
	artical_id
ORDER BY
	max_uv desc