日常场景中需要按照某个字段对不同行的数据进行汇总。
table: t_pv_uv_detailpage_count 表字段: belongLine:业务线 nowDate:日期
要求: 按照要求展示每天 TEG/集团的UV(其他业务线的去除), 并展示在不同的列中
方法一: 使用联合查询,按照日期联合查询
select a.uv as allUV, b.uv as tegUV, b.nowDate from
(select * from t_pv_uv_detailpage_count where belongLine ='集团' ) a
left join
(select * from t_pv_uv_detailpage_count where belongLine ='TEG') b on a.nowDate=b.nowDate order by a.nowDate desc;
方法二:巧用多个case重命名为多列,同时巧用 else 0,以及sum的方式来分组
步骤1: 使用多个case 重命名
select nowDate ,
case WHEN belongLine ='TEG' THEN uv
else 0 end as tegUV,
case WHEN belongLine ='集团' THEN uv
ELSE 0 end as allUV
from t_pv_uv_detailpage_count where (belongLine ='TEG' or belongLine ='集团') order by nowDate desc;
步骤2:上面会出现null值 ,这时候可巧用else 0 的方式将 'null' 换为0
select nowDate ,
case WHEN belongLine ='TEG' THEN uv
else 0 end as tegUV,
case WHEN belongLine ='集团' THEN uv
ELSE 0 end as allUV
from t_pv_uv_detailpage_count where (belongLine ='TEG' or belongLine ='集团') order by nowDate desc;
步骤3: 按照 newDate分组, 使用sum来聚合
select sum(allUV), sum(tegUV), nowDate from (
select nowDate ,
case WHEN belongLine ='TEG' THEN uv
else 0 end as tegUV,
case WHEN belongLine ='集团' THEN uv
ELSE 0 end as allUV
from t_pv_uv_detailpage_count where (belongLine ='TEG' or belongLine ='集团') order by nowDate desc
) as temp group by nowDate
以上两种sql都可以实现目标, 不过性能有所差别,使用explain查询
第一种联合查询,两次筛选 使用时长: 0.11s
第二种临时表,一次全表筛选 0.03s
显然第二个更快一些。