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

2019独角兽企业重金招聘Python工程师标准>>>

-- 
   写在最前边：
　　在第一次完成之后，看了下结果，感觉是正确的，没有找日期表核实日期所属周的问题，犯了个错误，
　　在看书后，突然意识到自己写的文章有问题，拿出来，再次验证，是自己写错误了，
　　
　　第二次的修正
-- 
-- 数据表
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)

 

转载于:https://my.oschina.net/u/1579560/blog/802629