MySQL按周统计 WEEK 实例

MySQL按周统计每周数据总和,用到了WEEK,subdate,date_format,date_sub,date_add函数。

  WEEK() 查看给定日期周数,语法:WEEK(date, mode);

  SUBDATE() 函数做日期减法操作,语法:SUBDATE(date,INTERVAL expr unit) 和 SUBDATE(expr,days);

  DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据,语法:DATE_FORMAT(date,format);

  DATE_SUB() 函数从日期减去指定的时间间隔,语法:DATE_SUB(date,INTERVAL expr type);

  DATE_ADD() 函数向日期添加指定的时间间,语法:DATE_ADD(date,INTERVAL expr type);

样例数据:

-- 创建数据样例表
CREATE TABLE `day_weight_tb` (
`day_date` varchar() DEFAULT NULL,
`in_weight` varchar() DEFAULT NULL,
`out_weight` varchar() DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 插入样例数据
INSERT INTO `day_weight_tb` (`day_date`, `in_weight`, `out_weight`) VALUES ('2018-03-10', '18.84', '74.14');
INSERT INTO `day_weight_tb` (`day_date`, `in_weight`, `out_weight`) VALUES ('2019-01-21', '17.69', '25.97');
INSERT INTO `day_weight_tb` (`day_date`, `in_weight`, `out_weight`) VALUES ('2019-02-01', '16.48', '10.0');
INSERT INTO `day_weight_tb` (`day_date`, `in_weight`, `out_weight`) VALUES ('2019-01-29', '10.9', '74.12');
INSERT INTO `day_weight_tb` (`day_date`, `in_weight`, `out_weight`) VALUES ('2019-02-13', '15.25', '16.62');
INSERT INTO `day_weight_tb` (`day_date`, `in_weight`, `out_weight`) VALUES ('2019-01-12', '19.21', '95.42');
INSERT INTO `day_weight_tb` (`day_date`, `in_weight`, `out_weight`) VALUES ('2019-02-14', '26.38', '20.59');
INSERT INTO `day_weight_tb` (`day_date`, `in_weight`, `out_weight`) VALUES ('2019-01-24', '15.99', '16.823');
INSERT INTO `day_weight_tb` (`day_date`, `in_weight`, `out_weight`) VALUES ('2019-01-13', '94.846', '56.256');
INSERT INTO `day_weight_tb` (`day_date`, `in_weight`, `out_weight`) VALUES ('2019-03-03', '15.63', '12.128');
INSERT INTO `day_weight_tb` (`day_date`, `in_weight`, `out_weight`) VALUES ('2019-03-12', '14.282', '24.046');
INSERT INTO `day_weight_tb` (`day_date`, `in_weight`, `out_weight`) VALUES ('2019-01-16', '14.045', '11.73');
INSERT INTO `day_weight_tb` (`day_date`, `in_weight`, `out_weight`) VALUES ('2019-03-08', '23.41', '23.441');
INSERT INTO `day_weight_tb` (`day_date`, `in_weight`, `out_weight`) VALUES ('2019-01-31', '11.177', '4332.26');
INSERT INTO `day_weight_tb` (`day_date`, `in_weight`, `out_weight`) VALUES ('2019-01-23', '11.8', '17104.49');
INSERT INTO `day_weight_tb` (`day_date`, `in_weight`, `out_weight`) VALUES ('2019-03-17', '17.95', '13.81');
INSERT INTO `day_weight_tb` (`day_date`, `in_weight`, `out_weight`) VALUES ('2019-03-01', '19.23', '20.43');
INSERT INTO `day_weight_tb` (`day_date`, `in_weight`, `out_weight`) VALUES ('2019-02-05', '21.55', '51.0');
INSERT INTO `day_weight_tb` (`day_date`, `in_weight`, `out_weight`) VALUES ('2019-02-27', '13.003', '23.32');
INSERT INTO `day_weight_tb` (`day_date`, `in_weight`, `out_weight`) VALUES ('2019-03-11', '16.44', '24.44');
INSERT INTO `day_weight_tb` (`day_date`, `in_weight`, `out_weight`) VALUES ('2019-02-28', '16.48', '23.4');
-- 创建结果表,插入统计结果
drop TABLE if exists week_count_tb;
CREATE TABLE `week_count_tb` (
`week_day` varchar() DEFAULT NULL,
`week_num` varchar() DEFAULT NULL,
`in_weight` varchar() DEFAULT NULL,
`out_weight` varchar() DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8; delete from week_count_tb;
INSERT INTO week_count_tb
SELECT
*
FROM
(
SELECT
middle.week_day,
middle.week_num,
sum(in_weight) in_weight,
sum(out_weight) out_weight
FROM
(
SELECT
subdate(
date_sub(day_date, INTERVAL DAY),
date_format(date_sub(day_date, INTERVAL DAY),'%w') - ) week_day,
WEEK (
date_add(subdate(
date_sub(day_date, INTERVAL DAY),
date_format(date_sub(day_date, INTERVAL DAY),'%w') - ),INTERVAL DAY),) week_num,
in_weight,
out_weight
FROM
day_weight_tb
) middle
GROUP BY
week_day,
week_num
) result;

思路:先确定一天是属于一年中的具体周数,再获取到一周中的周末日期,最后根据周数分组求和。

上一篇:Mutex — Windows API


下一篇:调试Windows Service