数据表列转行在统计中的应用

1、源数据结构

统计数据存储为：

 

id	day	adspace_id	hit_count
401	2012-10-1 0:00	7	7001
402	2012-10-2 0:00	4	4000
403	2012-10-3 0:00	5	5000
404	2012-10-6 0:00	7	7000
405	2012-10-8 0:00	3	2
409	2012-10-10 0:00	5	1170
413	2012-10-13 0:00	5	1183
417	2012-10-16 0:00	5	1093
421	2012-10-19 0:00	5	1120
425	2012-10-24 0:00	5	1190
429	2012-10-29 0:00	5	1164
484	2012-10-30 0:00	7	1
489	2012-10-31 0:00	7	1

广告位字典存储为：

Idname

2精品聚焦列表推荐位

3精品聚焦图片广告位

1置顶广告

4每日新品列表广告位

5每日新品图片广告位

7活动运营图片广告位

 

2、要实现转换后数据结构为：

 

 

3. mysql中行转换为列实现

将“广告位字典存储“数据读出，循环进行sql拼接以构造显示的列（可在sql中处理，也可在编程语言中处理，具体不描述，这个大家应该都懂得），最终实现sql语句为：

（1）按天分组

select a.day as days, sum(case adspace_id when 2 then hit_count else 0 end) as 精品聚焦列表推荐位,sum(case adspace_id when 3 then hit_count else 0 end) as 精品聚焦图片广告位,sum(case adspace_id when 1 then hit_count else 0 end) as 置顶广告,sum(case adspace_id when 4 then hit_count else 0 end) as 每日新品列表广告位,sum(case adspace_id when 5 then hit_count else 0 end) as 每日新品图片广告位,sum(case adspace_id when 7 then hit_count else 0 end) as 活动运营图片广告位,ifnull(sum(hit_count),0) as total from hi_stat_behaviour a

                            where 1=1  and a.day >= 1351180800  and a.day < 1351785600  group by a.day order by `day` desc

（2）按周分组

select `day` as days , sum(case adspace_id when 2 then hit_count else 0 end) as 精品聚焦列表推荐位,sum(case adspace_id when 3 then hit_count else 0 end) as 精品聚焦图片广告位,sum(case adspace_id when 1 then hit_count else 0 end) as 置顶广告,sum(case adspace_id when 4 then hit_count else 0 end) as 每日新品列表广告位,sum(case adspace_id when 5 then hit_count else 0 end) as 每日新品图片广告位,sum(case adspace_id when 7 then hit_count else 0 end) as 活动运营图片广告位, ifnull(sum(hit_count),0) as total from( 

                    select *,DATE_FORMAT(FROM_UNIXTIME(a.day),'%Y-%u') as g  ##--格式化为”年-周“格式

                    from hi_stat_behaviour a 

                    where 1=1  and a.day >= 1343577600  and a.day < 1352044800  ) as T group by g order by `day` desc

（3）按月分组

select `day` as days, sum(case adspace_id when 2 then hit_count else 0 end) as 精品聚焦列表推荐位,sum(case adspace_id when 3 then hit_count else 0 end) as 精品聚焦图片广告位,sum(case adspace_id when 1 then hit_count else 0 end) as 置顶广告,sum(case adspace_id when 4 then hit_count else 0 end) as 每日新品列表广告位,sum(case adspace_id when 5 then hit_count else 0 end) as 每日新品图片广告位,sum(case adspace_id when 7 then hit_count else 0 end) as 活动运营图片广告位,ifnull(sum(hit_count),0) as total from( 

                    select *,DATE_FORMAT(FROM_UNIXTIME(a.day),'%Y-%m') as g ##--格式化为”年-月“格式

                    from hi_stat_behaviour a 

                    where 1=1  and a.day >= 1333209600  and a.day < 1354291200  ) as T group by g order by `day` desc