主页 / dba / 问题 / 6045
Accepted
James Lee Vann
Asked: 2011-09-22 18:21:24 +0800 CST

从星期四或一周中的任何其他日子开始，如何通过每周结果改进我的 SQL 语句？

  • 772

我是一个完全的新手，我在任何地方都找不到这样做的好方法。我有一个数据库表，其中包含一周内不同时间记录的统计信息。报告周从星期四开始。该表包含一个日期戳列（日期），用于存储记录数据的时间。

我需要提取给定一周的数据（报告周从星期四开始）。我写了以下查询：

   SELECT * 
FROM `table` 
WHERE 1 = CASE 
  WHEN WEEKDAY(NOW()) = 0 THEN DATEDIFF(NOW(),`date`) BETWEEN -2 AND 4
  WHEN WEEKDAY(NOW()) = 1 THEN DATEDIFF(NOW(),`date`) BETWEEN -1 AND 5
  WHEN WEEKDAY(NOW()) = 2 THEN DATEDIFF(NOW(),`date`) BETWEEN -0 AND 6
  WHEN WEEKDAY(NOW()) = 3 THEN DATEDIFF(NOW(),`date`) BETWEEN -6 AND 0
  WHEN WEEKDAY(NOW()) = 4 THEN DATEDIFF(NOW(),`date`) BETWEEN -5 AND 1
  WHEN WEEKDAY(NOW()) = 5 THEN DATEDIFF(NOW(),`date`) BETWEEN -4 AND 2
  WHEN WEEKDAY(NOW()) = 6 THEN DATEDIFF(NOW(),`date`) BETWEEN -3 AND 3
END

这似乎适用于初始测试。但我不确定这是最好的方法。我对 MySQL 的性能了解不多，但是会有超过十万条记录需要过滤。由于检查的条件数量，这个查询真的很慢吗？

NOW() 函数在提取最新报告时使用 - 但是，在某些情况下，我需要在其他几周内进行报告 - 所以我会在该位置替换另一个日期。

此外，如果报告周发生更改，则以这种方式执行此操作需要重新编写查询 - 例如开始日更改为星期三。

我不能使用 WEEK() 函数，因为你只能在周日或周一用它开始一周。

非常感谢任何改进此查询的想法！

其他说明：目前使用 MariaDB 5.3。

mysql mariadb

3 个回答

  1. Best Answer

    这是我写的一个查询，给你最近的星期四和结束的星期三

    SELECT thuwk_beg + INTERVAL 0 second thu_beg,
thuwk_beg + INTERVAL 604799 second wed_end
FROM (SELECT (DATE(NOW()) - INTERVAL daysbacktothursday DAY) thuwk_beg
FROM (SELECT SUBSTR('3456012',wkndx,1) daysbacktothursday
FROM (SELECT DAYOFWEEK(dt) wkndx FROM (SELECT DATE(NOW()) dt) AAAA) AAA) AA) A;

    这是今天的一个例子，2011-09-21

    mysql> SELECT
    -> thuwk_beg + INTERVAL 0 second thu_beg,
    -> thuwk_beg + INTERVAL 604799 second wed_end
    -> FROM (SELECT (DATE(NOW()) - INTERVAL daysbacktothursday DAY) thuwk_beg
    -> FROM (SELECT SUBSTR('3456012',wkndx,1) daysbacktothursday
    -> FROM (SELECT DAYOFWEEK(dt) wkndx FROM (SELECT DATE(NOW()) dt) AAAA) AAA) AA) A;
+---------------------+---------------------+
| thu_beg             | wed_end             |
+---------------------+---------------------+
| 2011-09-15 00:00:00 | 2011-09-21 23:59:59 |
+---------------------+---------------------+
1 row in set (0.00 sec)

    只需将 NOW() 函数调用替换为您喜欢的任何日期时间，您将拥有从星期四开始的那一周，以便您选择给定的日期时间。

    这是使用特定日期“2011-01-01”的另一个示例

    mysql> SELECT
    -> thuwk_beg + INTERVAL 0 second thu_beg,
    -> thuwk_beg + INTERVAL 604799 second wed_end
    -> FROM (SELECT (DATE('2011-01-01') - INTERVAL daysbacktothursday DAY) thuwk_beg
    -> FROM (SELECT SUBSTR('3456012',wkndx,1) daysbacktothursday
    -> FROM (SELECT DAYOFWEEK(dt) wkndx FROM (SELECT DATE('2011-01-01') dt) AAAA) AAA) AA) A;
+---------------------+---------------------+
| thu_beg             | wed_end             |
+---------------------+---------------------+
| 2010-12-30 00:00:00 | 2011-01-05 23:59:59 |
+---------------------+---------------------+
1 row in set (0.00 sec)

    您table今天的引用查询类似于以下内容：

    SELECT * from `table`,
(SELECT thuwk_beg + INTERVAL 0 second thu_beg,
thuwk_beg + INTERVAL 604799 second wed_end
FROM (SELECT (DATE(NOW()) - INTERVAL daysbacktothursday DAY) thuwk_beg
FROM (SELECT SUBSTR('3456012',wkndx,1) daysbacktothursday
FROM (SELECT DAYOFWEEK(dt) wkndx FROM (SELECT DATE(NOW()) dt) AAAA) AAA) AA) A) M
WHERE `date` >= thu_beg
AND `date` <= wed_end;

    试试看 ！！！

    更新 2011-09-22 16:27 EDT

    这是我提出的标记周四至周三的查询。

    SELECT thuwk_beg + INTERVAL 0 second thu_beg,
thuwk_beg + INTERVAL 604799 second wed_end
FROM (SELECT (DATE(NOW()) - INTERVAL daysbacktothursday DAY) thuwk_beg
FROM (SELECT SUBSTR('3456012',wkndx,1) daysbacktothursday
FROM (SELECT DAYOFWEEK(dt) wkndx FROM (SELECT DATE(NOW()) dt) AAAA) AAA) AA) A;

    其他周呢？？？

    • (SELECT SUBSTR('6012345',wkndx,1)从周一到周日的那一周
    • (SELECT SUBSTR('5601234',wkndx,1)从周二开始到周一结束的那一周
    • (SELECT SUBSTR('4560123',wkndx,1)从周三开始到周二结束的那一周
    • (SELECT SUBSTR('3456012',wkndx,1)从周四开始到周三结束的那一周
    • (SELECT SUBSTR('2345601',wkndx,1)从周五开始到周四结束的那一周
    • (SELECT SUBSTR('1234560',wkndx,1)从周六开始到周五结束的那一周
    • (SELECT SUBSTR('0123456',wkndx,1)从周日开始到周六结束的那一周
    • 5

  2. 让我重申一下您的要求，以确保我做对了：

    您想提取指定日期过去 7 天的所有记录吗？

    它可能看起来像：

    select * from table where `date` between $date - interval 7 day and $date

    重要的是要注意 $date 不是文字 mysql 语法，而只是您想要的开始日期的示例占位符。如果这是为了报告，我想查询最终会从某个脚本生成吗？如果这是真的，那么在该语言中可能会更简单，然后将文字值作为构造查询的一部分传递。

    我喜欢让查询尽可能简单，所以我会这样。我将为其他人留出空间来贡献答案，以在单个 SQL-fu 查询中完成您想要的。

    编辑：重读您的帖子后，您似乎正在使用日期类型。在这种情况下，以下斜体字块可能是多余的。如果它对其他人有帮助，我会留下它（因为我花时间写它:-)

    您说您使用的是“日期戳”？这在技术上不是 Mysql 数据类型。它是日期时间、时间戳还是日期（时间和年份也存在，但从您的上下文来看，我觉得这些不适用）？我问是因为你可能想让它只是一个日期列而不是其他列。如果这对您来说是正确的选择，那么实际上取决于如何使用 columnn 以及整个查询的表的详细信息。如果它的唯一目的只是在一个日期范围内提取记录，而不考虑时间，那么 date 绝对是要走的路。一方面，它只需要 3 个字节而不是 4 或 8 个字节。如果日期符合您的使用要求（即您不关心时间部分），我可以详细说明您想要使用日期的其他原因。您可以在以下位置找到有关不同类型的详细信息 http://dev.mysql.com/doc/refman/5.0/en/datetime.html

    http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html

    当且仅当您专门使用日期时，您可能会考虑以下事项：

    运行以“解释”为前缀的查询。有关如何解释输出以及什么是最好的详细信息，请访问http://dev.mysql.com/doc/refman/5.0/en/explain-output.html

    一旦你有了解释，将查询更改为

    select * from table where date in ("N", "N+1"..."N+7")

    您在其中枚举您感兴趣的所有单个日期。我遇到过一些情况，很明显 MySQL 不够聪明，无法有效地使用范围查询（即在 x 和 y 之间）与枚举小集合的特定值的情况。

    如果您根据其值进行定期报告查询，您将希望确保该列被索引，无论哪种用例。

    • 1

  3. @Rolando 先生显然回答了这个问题，但我会提出另一个决定，允许您在不同时区定义的时区和最重要的按周之间操作和自定义日历

    因此，假设您的 mySQL 在 UTC 配置的服务器上运行，并且您希望有一个提前 7 小时的自定义日历，因此您的周应从星期六早上 7 点开始

    CREATE TABLE `wh_blur_calendar` (
  `date` timestamp NOT NULL ,
  `y` smallint(6) DEFAULT NULL,
  `q` tinyint(4) DEFAULT NULL,
  `m` tinyint(4) DEFAULT NULL,
  `d` tinyint(4) DEFAULT NULL,
  `w` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `wh_ints` (
  `i` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into wh_ints values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

    现在流行的笛卡尔连接应该填充你的表：

    INSERT INTO wh_blur_calendar (date)
SELECT DATE('2010-01-01 00:00:00 ') + INTERVAL a.i*10000 + b.i*1000 + c.i*100 + d.i*10 + e.i DAY
FROM wh_ints a JOIN wh_ints b JOIN wh_ints c JOIN wh_ints d JOIN wh_ints e
WHERE (a.i*10000 + b.i*1000 + c.i*100 + d.i*10 + e.i) < 10245
ORDER BY 1;

    让我们更新时间：

    update  db_wh_repo.wh_blur_calendar set date = date_add(date, interval 7 hour);

    最后以自定义方式安排您的日历周

    UPDATE wh_blur_calendar
SET 
    y = YEAR(date),
    q = quarter(date),
    m = MONTH(date),
    d = dayofmonth(date),
    w = week(date_add((date), interval 1 day));

    相信我，我花了几个小时做出这个决定，但如果您想根据自定义时区和星期定义对结果进行分组，它会给您很大的自由度。

    • 1

相关问题