文档章节

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

小张525
 小张525
发布于 2016/12/08 22:03
字数 779
阅读 135
收藏 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
博文 101
码字总数 32841
作品 0
乌鲁木齐
程序员
mysql中timestamp字段按周统计数据

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

bobway
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
oracle 一个月的日期周期内按照周分组

给定一个月: 2016-06 星期一为一周的第一天, 这个月的日期周期内按周分组, 得到每一周的日期区间。 如下 : 2016-06-01 2016-06-05 2016-06-06 2016-06-12 2016-06-13 2016-06-19 2016-0...

小毅子
2016/06/23
338
0
mysql中date_format对日期进行格式化

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

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

没有更多内容

加载失败,请刷新页面

加载更多

多线程

1. 多线程概念。并发和并行的概念。 多线程指的是一段时间内cpu同时执行多个线程。一个程序至少运行>=1个进程,进程就是运行中的程序,而一个进程至少运行>=1个线程,线程是操作系统能调度的...

鱼想吃肉
37分钟前
0
0
HBase 表修复在线方式和离线方式

一、在线修复 1.1 使用检查命令 $ ./bin/hbase hbck 该命令可完整修复 HBase 元数据信息;存在有错误信息会进行输出; 也可以通过如下命令查看详细信息: $ ./bin/hbase hbck -details 1.2 ...

Ryan-瑞恩
今天
2
0
redis 系列二 -- 常用命令

1.基础命令 info ping quit save dbsize select flushdb flushall 2.键命令 2.1 set 直接赋值 set a a 2.2 get 取值 get a 2.3 exists 是否存在 exists a 2.4 expire 设置剩余时间 秒 expire......

imbiao
今天
2
0
php foreach

<?php// 数组的引用$a=array(1,2,3,4,5);foreach($a as $key=>&$value){$value=$value*2;}print_r($a);echo " $key -------------------$value\r\n";/** * ...

小张525
今天
3
0
12-利用思维导图梳理JavaSE-多线程

12-利用思维导图梳理JavaSE-多线程 主要内容 1.线程概念 2.线程开发 3.线程的状态 4.线程的同步和死锁 5.Java5.0并发库类 QQ/知识星球/个人WeChat/公众号二维码 本文为原创文章,如果对你有一...

飞鱼说编程
今天
1
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部