文档章节

mysql 日期按周分组,找出数据中该周的第一天

小张525
 小张525
发布于 2016/12/08 22:03
字数 779
阅读 144
收藏 1
-- 
   写在最前边:
  在第一次完成之后,看了下结果,感觉是正确的,没有找日期表核实日期所属周的问题,犯了个错误,
  在看书后,突然意识到自己写的文章有问题,拿出来,再次验证,是自己写错误了,
  
  第二次的修正
-- 
-- 数据表
create table  sales(
	id int auto_increment not null,
    date datetime not null,
    cost int unsigned not null,
    primary key (id)
)engine=myisam;

-- 插入数据
insert into sales(date,cost) values('2010-12-27',100),('2010-12-28',100),('2010-12-29',100),('2010-12-30',100),('2010-12-31',100),('2011-01-03',200),('2011-01-02',100),('2011-01-01',100),('2011-01-04',100),('2011-01-05',100),('2011-01-06',100),('2011-01-10',100);

-- 数据按周分组处理
select * from sales group by week(date);
-- 按周分组处理, 显示所在的周,以及在本周出现的次数.
select week(date) as week , date as da, count(*) as count from sales group by week(date);
-- 按周分组, 如果有重复的,显示在该周出现的第一个.
-- 先按周分组处理, 如果分过组了,就看不到具体的内容了,
select id,date,cost,week(date) from sales group by week(date)  ;
-- 如何体现一周中最早的一天.该周的统计次数.
select id, a.cnt, (select min(date)  from  sales as b where week(b.date) = a.week  ) as d  from  (select id,count(*) as cnt,week(date) as week from sales group by week(date)) as a;


-- output 
id  cnt  date
8	1	2011-01-01 00:00:00
6	5	2011-01-02 00:00:00
12	1	2011-01-10 00:00:00
1	5	2010-12-27 00:00:00

-- explain   (alter table sale add index_date(`date` asc));
+----+--------------------+------------+-------+---------------+------------+---------+------+------+---------------------------------+
| id | select_type        | table      | type  | possible_keys | key        | key_len | ref  | rows | Extra                           |
+----+--------------------+------------+-------+---------------+------------+---------+------+------+---------------------------------+
|  1 | PRIMARY            | <derived3> | ALL   | NULL          | NULL       | NULL    | NULL |   12 | NULL                            |
|  3 | DERIVED            | sales      | ALL   | index_date    | NULL       | NULL    | NULL |   12 | Using temporary; Using filesort |
|  2 | DEPENDENT SUBQUERY | b          | index | NULL          | index_date | 5       | NULL |   12 | Using where; Using index        |
+----+--------------------+------------+-------+---------------+------------+---------+------+------+---------------------------------+



-- 正确的结果 
SELECT 
    id,
    a.cnt,
    (SELECT 
            MIN(date)
        FROM
            sales AS b
        WHERE
            FLOOR(DATEDIFF(b.date, '1990-01-01') / 7) = a.week) AS d
FROM
    (SELECT 
        id,
            COUNT(*) AS cnt,
            FLOOR(DATEDIFF(date, '1990-01-01') / 7) AS week
    FROM
        sales
    GROUP BY FLOOR(DATEDIFF(date, '1990-01-01') / 7)) AS a;

-- output
+----+-----+---------------------+
| id | cnt | d                   |
+----+-----+---------------------+
|  1 |   7 | 2010-12-27 00:00:00 |
|  6 |   4 | 2011-01-03 00:00:00 |
| 12 |   1 | 2011-01-10 00:00:00 |
+----+-----+---------------------+
3 rows in set (0.00 sec)

-- index
mysql> show index from sales;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| sales |          0 | PRIMARY    |            1 | id          | A         |          12 |     NULL | NULL   |      | BTREE      |         |               |
| sales |          1 | index_date |            1 | date        | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)



-- explain 
+----+--------------------+------------+-------+---------------+------------+---------+------+------+---------------------------------+
| id | select_type        | table      | type  | possible_keys | key        | key_len | ref  | rows | Extra                           |
+----+--------------------+------------+-------+---------------+------------+---------+------+------+---------------------------------+
|  1 | PRIMARY            | <derived3> | ALL   | NULL          | NULL       | NULL    | NULL |   12 | NULL                            |
|  3 | DERIVED            | sales      | ALL   | index_date    | NULL       | NULL    | NULL |   12 | Using temporary; Using filesort |
|  2 | DEPENDENT SUBQUERY | b          | index | NULL          | index_date | 5       | NULL |   12 | Using where; Using index        |
+----+--------------------+------------+-------+---------------+------------+---------+------+------+---------------------------------+
3 rows in set (0.01 sec)

-- 关于周的测试
SELECT 
    DATE_ADD('1900-01-01',
        INTERVAL FLOOR(DATEDIFF(date, '1900-01-01') / 7) * 7 DAY) AS week_start,
    DATE_ADD('1990-01-01',
        INTERVAL FLOOR(DATEDIFF(date, '1900-01-01') / 7) * 7 + 6 DAY) AS week_end,
    SUM(cost)
FROM
    sales
GROUP BY FLOOR(DATEDIFF(date, '1900-01-01') / 7);


-- output
+------------+------------+-----------+
| week_start | week_end   | sum(cost) |
+------------+------------+-----------+
| 2010-12-27 | 2101-01-02 |       700 |
| 2011-01-03 | 2101-01-09 |       500 |
| 2011-01-10 | 2101-01-16 |       100 |
+------------+------------+-----------+

-- index
mysql> show index from sales;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| sales |          0 | PRIMARY    |            1 | id          | A         |          12 |     NULL | NULL   |      | BTREE      |         |               |
| sales |          1 | index_date |            1 | date        | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)


-- explain
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | SIMPLE      | sales | ALL  | index_date    | NULL | NULL    | NULL |   12 | Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
1 row in set (0.00 sec)







 

© 著作权归作者所有

共有 人打赏支持
小张525
粉丝 6
博文 103
码字总数 33503
作品 0
乌鲁木齐
程序员
私信 提问
mysql中timestamp字段按周统计数据

表的类型 以字段dtEventTime按周来统计,表中的记录的条数. 结果: DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据。 DATE_FORMAT(date,format) date 参数是合法的日期。format 规定日期...

bobway
2018/07/09
0
0
mysql中DATE_FORMAT用法根据时间按周、月、年等查询

定义和用法 DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据。 语法 DATE_FORMAT(date,format) date 参数是合法的日期。format 规定日期/时间的输出格式。 可以使用的格式有: 格式 描...

LCZ777
2016/09/09
29
0
mysql中DATE_FORMAT()和str_to_date() 、to_days

定义和用法 DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据。 语法 DATE_FORMAT(date,format) date 参数是合法的日期。format 规定日期/时间的输出格式。 可以使用的格式有: 格式 描...

huluobotx
2015/09/24
721
0
MySQL-9个时间函数,帮你查找数据

  前言 在MySQL查询数据的时候,我们一般都会选择查询一段时间的数据,这时候就涉及到时间的取值。MySQL带有一些函数可以让我们更轻松的获取对应的时间。       时间函数 1、NOW 获取当...

linux运维菜
2018/08/09
0
0
mysql中date_format对日期进行格式化

一、语法 DATEFORMAT(date,format) 二、参数说明 1、date:合法的日期或日期字符串 2、format:规定日期/时间的输出格式,常见的输出格式有: %a 缩写星期名 %b 缩写月名 %c 月,数值 %D 带有...

学习也休闲
2016/03/01
490
0

没有更多内容

加载失败,请刷新页面

加载更多

TiDB 3.0 Beta Release Notes

2019 年 1 月 19 日,TiDB 发布 3.0 Beta 版,对应 master branch 的 TiDB-Ansible。相比 2.1 版本,该版本对系统稳定性、优化器、统计信息以及执行引擎做了很多改进。 TiDB 新特性 支持 Vi...

TiDB
20分钟前
2
0
从拼多多优惠券事件看到的一些反思

本文由云+社区发表 作者:颜国平 摘要:最近几年,电商行业飞速发展,各种创业公司犹如雨后春笋大量涌现,商家通过各种活动形式的补贴来获取用户、培养用户的消费习惯,即将到来的“ 购物狂欢...

腾讯云加社区
21分钟前
4
0
记录一次BUG

1: 请求注册页面时生成一个UUID, 并且将UUID隐藏在页面中并且添加到session中去。 2: 发送短信时获取手机号和UUID,将两者发送至服务器 3: 先判断UUID和session中UUID是否一致。 不一致就...

专业写BUG的程序员
26分钟前
1
0
阿里云漏洞提示:phpMyAdmin <=4.8.1 checkPageValidity函数缺陷可导致GETSHELL

如题: phpMyAdmin <=4.8.1 后台checkPageValidity函数缺陷可导致GETSHELL 即:checkPageValidity函数对外部输入过滤不严,可导致本地包含任意文件。进一步地攻击者可通过注入代码到特定文件...

408582708
26分钟前
1
0
PyTorch可视化理解卷积神经网络

摘要: 神经网络工具像一个黑匣子,无法知道它的中间是如何处理的。本文使用图片加代码的形式讲解CNN网络,并对每层的输出进行可视化,便于初学者理解,可以动手实践下哦! 如今,机器已经能...

阿里云官方博客
38分钟前
1
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部