如何在不进行硬编码的情况下获取时间范围数据

Question

我有如下查询。现在我想更改3 days ago from now()

我总是硬编码如下

select 
id
,coalesce(sum(case when snapshot_day in ('2021-05-03') then 1 else 0 end), 0) AS "2021-05-03"
,coalesce(sum(case when snapshot_day in ('2021-05-04') then 1 else 0 end), 0) AS "2021-05-04"
,coalesce(sum(case when snapshot_day in ('2021-05-05') then 1 else 0 end), 0) AS "2021-05-05"
from talble
where type in ('Daily')
group by 1

有没有什么好的方法可以在不进行硬编码的情况下得到结果？

如果有一些例子，并且有人有意见，请让我知道

感谢

Answer 1

您现在可以使用((：

select 
id
,coalesce(sum(case when snapshot_day = cast(now() as date) - interval 2 day  then 1 else 0 end), 0) AS "Day before yesterday"
,coalesce(sum(case when snapshot_day = cast(now() as date) - interval 1 day  then 1 else 0 end), 0) AS yesterday
,coalesce(sum(case when snapshot_day = cast(now() as date) then 1 else 0 end), 0) AS today
from talble
where type in ('Daily')
and snapshot_day >= cast(now() as date) - interval 2 day
group by id

Answer 2

您可以使用PREPARED STATION来执行此操作：

我在WHERE子句中添加了一个比较，以便从表中只获得3天。

SELECT CONCAT("SELECT id ",
",coalesce( sum( IF( snapshot_day = date(now() - INTERVAL 3 DAY), 1 ,0) )) AS '",date(now() - INTERVAL 3 DAY),"' ",
",coalesce( sum( IF( snapshot_day = date(now() - INTERVAL 2 DAY), 1 ,0) )) AS '",date(now() - INTERVAL 2 DAY),"' ",
",coalesce( sum( IF( snapshot_day = date(now() - INTERVAL 1 DAY), 1 ,0) )) AS '",date(now() - INTERVAL 1 DAY),"' ",
"FROM talble ",
"WHERE type in ('Daily') ",
" AND snapshot_day BETWEEN DATE(NOW()) - INTERVAL 3 DAY + INTERVAL 0 SECOND ",
"AND DATE(NOW()) - INTERVAL 0 DAY - INTERVAL 1 SECOND "
"GROUP BY 1") INTO @myquery;
SELECT @myquery;
PREPARE stmt FROM @myquery;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

样本

MariaDB [Bernd]> SELECT * FROM talble;
+----+-------+---------------------+
| id | type  | snapshot_day        |
+----+-------+---------------------+
|  1 | Daily | 2021-05-04 00:00:00 |
|  2 | Daily | 2021-05-05 00:00:00 |
|  3 | Daily | 2021-05-05 00:00:00 |
|  4 | Daily | 2021-05-06 00:00:00 |
|  5 | Daily | 2021-05-06 00:00:00 |
|  6 | Daily | 2021-05-04 00:00:00 |
|  7 | Daily | 2021-05-07 00:00:00 |
+----+-------+---------------------+
7 rows in set (0.02 sec)

MariaDB [Bernd]> SELECT CONCAT("SELECT id ",
-> ",coalesce( sum( IF( snapshot_day = date(now() - INTERVAL 3 DAY), 1 ,0) )) AS '",date(now() - INTERVAL 3 DAY),"' ",
-> ",coalesce( sum( IF( snapshot_day = date(now() - INTERVAL 2 DAY), 1 ,0) )) AS '",date(now() - INTERVAL 2 DAY),"' ",
-> ",coalesce( sum( IF( snapshot_day = date(now() - INTERVAL 1 DAY), 1 ,0) )) AS '",date(now() - INTERVAL 1 DAY),"' ",
-> "FROM talble ",
-> "WHERE type in ('Daily') ",
-> "GROUP BY 1") INTO @myquery;
Query OK, 1 row affected (0.01 sec)

MariaDB [Bernd]> 
MariaDB [Bernd]> SELECT @myquery;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @myquery                                                                                                                                                                                                                                                                                                                               |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT id ,coalesce( sum( IF( snapshot_day = date(now() - INTERVAL 3 DAY), 1 ,0) )) AS '2021-05-04' ,coalesce( sum( IF( snapshot_day = date(now() - INTERVAL 2 DAY), 1 ,0) )) AS '2021-05-05' ,coalesce( sum( IF( snapshot_day = date(now() - INTERVAL 1 DAY), 1 ,0) )) AS '2021-05-06' FROM talble WHERE type in ('Daily') GROUP BY 1 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [Bernd]> 
MariaDB [Bernd]> PREPARE stmt FROM @myquery;
Query OK, 0 rows affected (0.01 sec)
Statement prepared

MariaDB [Bernd]> EXECUTE stmt;
+----+------------+------------+------------+
| id | 2021-05-04 | 2021-05-05 | 2021-05-06 |
+----+------------+------------+------------+
|  1 |          1 |          0 |          0 |
|  2 |          0 |          1 |          0 |
|  3 |          0 |          1 |          0 |
|  4 |          0 |          0 |          1 |
|  5 |          0 |          0 |          1 |
|  6 |          1 |          0 |          0 |
|  7 |          0 |          0 |          0 |
+----+------------+------------+------------+
7 rows in set (0.00 sec)

MariaDB [Bernd]> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)

MariaDB [Bernd]>