MySQL_时间段维度统计

来源：互联网发布：淘宝手机优惠券编辑：程序博客网时间：2024/05/29 16:52

MySQL#时间维度统计

场景

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

解决

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

2.业务数据统计

3.依据时间维度join

示例

set @i := -1;selectg1.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_quantityfrom    (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) g1left 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_strleft 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_strorder by g1.date_str;set @i := -1;

效果

0 0