一段日期内星期都是周五的客流信息

-- 返回从beginDate到endDate，每小时的平均客流(group by 小时就可以了,sum(innum)/count(1))
SELECT
TRUNCATE (SUM(rsh.innum) / COUNT(1), 2) AS innum,
DATE_FORMAT(rsh.datatime, '%k:00') xstr
FROM
report_site_hour rsh
WHERE
relationid = 'Hn_P00001'
AND datatime BETWEEN '2017-09-01'
AND DATE_FORMAT(
'2017-09-30 23:59:59',
'%Y-%m-%d 23:59:59'
)
GROUP BY
DATE_FORMAT(rsh.datatime, '%H:%i:%s');

-- 一段日期内星期都是周五的客流信息

固定星期(如传入5,即周五每个9:00,10:00,11:00)的每小时的平均客流

SELECT TRUNCATE((SUM(rsh.innum)/COUNT(1)),2) AS innum, 
DATE_FORMAT(rsh.datatime, '%k:00') xstr
 FROM report_site_hour rsh LEFT JOIN dimdate dd
 ON DATE_FORMAT(rsh.datatime, '%Y%m%d') = dd.DateKey 
WHERE relationid = 'Hn_P00001' 
AND datatime BETWEEN '2017-09-01' 
AND DATE_FORMAT('2017-09-30', '%Y-%m-%d 23:59:59')
AND dd.DayNumberOfWeek = 5 GROUP BY DATE_FORMAT(rsh.datatime,'%H:%i:%s')

下表是小时表关联星期用到的表

-- 查询天表,9月份星期平均客流(相同星期加和平均)

SELECT
dd.DayNameOfWeek AS xstr,
TRUNCATE(SUM(rsd.innum) / COUNT(1),2)
FROM
report_site_day rsd
LEFT JOIN dimdate dd ON DATE_FORMAT(rsd.datatime, '%Y%m%d') = dd.DateKey
WHERE
relationid = 'Hn_P00001'
AND datatime BETWEEN '2017-09-01'
AND '2017-09-30'
GROUP BY
dd.DayNameOfWeek

-- 这里是查询小时表,和上面查询天表,结果一样,但天表的sql简单(因为集群每隔1小时汇小时表数据时,把天表也汇了,可直接查询天表,更为简单)

SELECT 
t.wk AS xstr,
SUM(CASE WHEN t.kl>0 THEN t.kl ELSE 0 END) zhoukltotal,
COUNT(1) aa,
TRUNCATE(SUM(CASE WHEN t.kl>0 THEN t.kl ELSE 0 END)/COUNT(1),2) AS innum


 FROM (
SELECT DATE_FORMAT(rsh.datatime, '%Y-%m-%d') rq,
SUM(CASE WHEN rsh.innum>0 THEN rsh.innum ELSE 0 END) kl,
dd.DayNameOfWeek wk
 FROM report_site_hour rsh LEFT JOIN dimdate dd ON DATE_FORMAT(rsh.datatime, '%Y%m%d') = dd.DateKey 
WHERE
relationid = 'Hn_P00001'
AND datatime BETWEEN '2017-09-01'
AND '2017-09-30 23:59:59' GROUP BY DATE_FORMAT(rsh.datatime, '%Y-%m-%d') 


) t GROUP BY t.wk