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.goods_ctime < '2015-01-01'
group by date(a.goods_ctime)) g2 ON g2.q_time = g1.date_str
left join
(select
date(b.order_ctime) ctime,
sum(c.shopping_quantity * c.sku_final_price_cny) sku_final_price_cny,
sum(c.shopping_quantity * c.sku_list_price_cny) sku_list_price_cny,
sum(c.shopping_quantity) shopping_quantity
from
t_order b
left join t_order_item c ON c.order_id = b.order_id
where
b.order_state > 0
and b.order_status >= 2
and b.order_ctime >= '2014-01-01 00:00:00'
and b.order_ctime < '2015-01-01 00:00:00'
group by date(b.order_ctime)) g3 ON g3.ctime = g1.date_str
order by g1.date_str;
set @i := -1;
效果