文档章节

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

小张525
 小张525
发布于 2016/12/08 22:03
字数 779
阅读 130
收藏 1
点赞 0
评论 0
-- 
   写在最前边:
  在第一次完成之后,看了下结果,感觉是正确的,没有找日期表核实日期所属周的问题,犯了个错误,
  在看书后,突然意识到自己写的文章有问题,拿出来,再次验证,是自己写错误了,
  
  第二次的修正
-- 
-- 数据表
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
博文 59
码字总数 32032
作品 0
乌鲁木齐
程序员
mysql中DATE_FORMAT用法根据时间按周、月、年等查询

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

LCZ777 ⋅ 2016/09/09 ⋅ 0

mysql中DATE_FORMAT()和str_to_date() 、to_days

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

huluobotx ⋅ 2015/09/24 ⋅ 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 ⋅ 0

mysql中date_format对日期进行格式化

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

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

MySQL的跨年周统计问题

在mysql中,如果要查询的表中只有日期字段,但是业务需求要按照周分组,排序的话,mysql提供了多种方法;1。date_formatDATE_FORMAT(date, format) 函数根据format字符串格式化date值。这里可...

chro008 ⋅ 02/08 ⋅ 0

Oracle日期周详解以及周开始结束时间计算

1 ORACLE中周相关知识描述 1.1 日期格式化函数 TO_CHAR(X [,FORMAT]):将X按FORMAT格式转换成字符串。X是一个日期,FORMAT是一个规定了X采用何种格式转换的格式字符串,FORMAT与周相关的有W...

壹峰 ⋅ 04/28 ⋅ 0

mysql将int 时间类型格式化

DATE_FORMAT(date,format) 根据format字符串安排date值的格式。 select from_unixtime(time,'%Y-%m-%d %H:%i:%s'); select from_unixtime(now(),'%Y-%m-%d %H:%i:%s'); 以下说明符可用在 fo......

大朱 ⋅ 2014/03/29 ⋅ 0

MySQL日期 字符串 时间戳互转

涉及的函数 dateformat(date, format) 函数,MySQL日期格式化函数dateformat() unixtimestamp() 函数 strtodate(str, format) 函数 fromunixtime(unixtimestamp, format) 函数,MySQL时间戳格......

李矮矮 ⋅ 2016/09/19 ⋅ 0

mysql DATE_FORMAT(date,format) 函数

今天遇到了使用 mysql dateformate(date,formate) 函数,摘录一下,希望对你有用。更为详细的解说,可以参考 这里 date 参数是合法的日期。format 规定日期/时间的输出格式。...

peiquan ⋅ 2014/04/02 ⋅ 0

按天、按周、按月、按季、按年分期别统计

在面对大数据量按期别统计时,数据库表中往往只有一个日期列,如果在统计时直接使用tochar函数转换成期别进行group by会导致SQL执行较慢,因为tochar后用不上索引,而大部分时候我们只建立一...

无知有趣 ⋅ 2014/05/23 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

浅谈springboot Web模式下的线程安全问题

我们在@RestController下,一般都是@AutoWired一些Service,由于这些Service都是单例,所以并不存在线程安全问题。 由于Controller本身是单例模式 (非线程安全的), 这意味着每个request过来,...

算法之名 ⋅ 今天 ⋅ 0

知乎Java数据结构

作者:匿名用户 链接:https://www.zhihu.com/question/35947829/answer/66113038 来源:知乎 著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。 感觉知乎上嘲讽题主简...

颖伙虫 ⋅ 今天 ⋅ 0

Confluence 6 恢复一个站点有关使用站点导出为备份的说明

推荐使用生产备份策略。我们推荐你针对你的生产环境中使用的 Confluence 参考 Production Backup Strategy 页面中的内容进行备份和恢复(这个需要你备份你的数据库和 home 目录)。XML 导出备...

honeymose ⋅ 今天 ⋅ 0

JavaScript零基础入门——(九)JavaScript的函数

JavaScript零基础入门——(九)JavaScript的函数 欢迎回到我们的JavaScript零基础入门,上一节课我们了解了有关JS中数组的相关知识点,不知道大家有没有自己去敲一敲,消化一下?这一节课,...

JandenMa ⋅ 今天 ⋅ 0

火狐浏览器各版本下载及插件httprequest

各版本下载地址:http://ftp.mozilla.org/pub/mozilla.org//firefox/releases/ httprequest插件截至57版本可用

xiaoge2016 ⋅ 今天 ⋅ 0

Docker系列教程28-实战:使用Docker Compose运行ELK

原文:http://www.itmuch.com/docker/28-docker-compose-in-action-elk/,转载请说明出处。 ElasticSearch【存储】 Logtash【日志聚合器】 Kibana【界面】 答案: version: '2'services: ...

周立_ITMuch ⋅ 今天 ⋅ 0

使用快嘉sdkg极速搭建接口模拟系统

在具体项目研发过程中,一旦前后端双方约定好接口,前端和app同事就会希望后台同事可以尽快提供可供对接的接口方便调试,而对后台同事来说定好接口还仅是个开始、设计流程,实现业务逻辑,编...

fastjrun ⋅ 今天 ⋅ 0

PXE/KickStart 无人值守安装

导言 作为中小公司的运维,经常会遇到一些机械式的重复工作,例如:有时公司同时上线几十甚至上百台服务器,而且需要我们在短时间内完成系统安装。 常规的办法有什么? 光盘安装系统 ===> 一...

kangvcar ⋅ 昨天 ⋅ 0

使用Puppeteer撸一个爬虫

Puppeteer是什么 puppeteer是谷歌chrome团队官方开发的一个无界面(Headless)chrome工具。Chrome Headless将成为web应用自动化测试的行业标杆。所以我们很有必要来了解一下它。所谓的无头浏...

小草先森 ⋅ 昨天 ⋅ 0

Java Done Right

* 表示难度较大或理论性较强。 ** 表示难度更大或理论性更强。 【Java语言本身】 基础语法,面向对象,顺序编程,并发编程,网络编程,泛型,注解,lambda(Java8),module(Java9),var(...

风华神使 ⋅ 昨天 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部