基于MySql的数据统计小结
SELECT md5(concat_ws('_','$day',city_id,product_id,product_type_id,product_level_id)),$day,a.city_id,b.name,product_id,product_name,product_type_id,d.name,product_level_id,e.name,
sum(i_total_num),sum(i_car_in_running),sum(i_car_in_rental),sum(i_car_in_short_outage),sum(i_car_in_maintain),sum(i_car_in_annual_check),sum(i_car_in_repair),
sum(i_car_in_violation),sum(i_car_in_short_other),sum(i_car_in_long_outage)
FROM dwm_car_product_state_number as a left join t_rs_city as b on a.city_id=b.id
left join t_rs_product_type as d on a.product_type_id=d.id
left join t_rs_product_level as e on a.product_level_id=e.id
GROUP BY city_id,product_id,product_type_id,product_level_id
1. 统计时细化到原子统计单位，此统计表的主键即为统计单位的主键
2. 按维度聚合时，拼接所有维度计算签名来作为统计聚合表的主键，这样做可以避免数据重复，尤其是在更新数据时，可以不用先删除数据
3. load data infile 'xxxx' ignore/replace into table... 通过指定replace来更新数据
分组后取第一条查询问题
--方法1：select a.* from tb a where val = (select max(val) from tb where name = a.name) order by a.name
--方法2：
select a.* from tb a where not exists(select 1 from tb where name = a.name and val > a.val)
--方法3：
select a.* from tb a,(select name,max(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--方法4：
select a.* from tb a inner join (select name , max(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name
# 所有日期字段更新为10天后
UPDATE dm_team_recommend SET vday=DATE_ADD(vday,INTERVAL 10 DAY)
页:
[1]