mysql_时间段维度统计_MySQL_时间段维度统计

MySQL#时间维度统计

场景

给定某一时间段对这段时间内每一天的数据做聚合统计。

解决

1.构造精确到天的时间区间(取任意行数大于31的表，通过子查询和自定义变量即可构造特定日期行数)

2.业务数据统计

3.依据时间维度join

示例

set @i := -1;

select

g1.date_str,

ifnull(g2.q_ct,0) q_ct,

ifnull(g3.sku_final_price_cny, 0) sku_final_price_cny,

ifnull(g3.sku_list_price_cny, 0) sku_list_price_cny,

ifnull(g3.shopping_quantity, 0) shopping_quantity

from

(select

date(date_add(str_to_date('2014-11-01', '%Y-%m-%d'), interval @i:=@i + 1 day)) as date_str

from

(select

1

from

t_account b

limit 1 , 30) b) g1

left join

(SELECT

date(a.goods_ctime) q_time, count(1) q_ct

FROM

t_goods a

where

1 = 1 and a.goods_ctime >= '2014-01-01'

and a.go