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.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;

效果