英文:
Find how many x-day intervals have passed since outset and another date
问题
在SQL中，我可以如何计算自起始日期以来经过了多少个x天的时间间隔？
以“2023-01-11”作为起始日期的示例。对于任何后续日期，我想知道自起始日期以来经过了多少个4天的时间间隔。
例如：
2023-01-13应返回1，因为这是第一个4天的时间间隔。2023-01-18应返回2，因为这是第二个4天的时间间隔。2023-02-02应返回6，因为这是第六个4天的时间间隔。
如果我有相应的SQL表格：
CREATE TABLE my_tbl (outset_date DATE, date_of_interest DATE);
INSERT INTO my_tbl (outset_date, date_of_interest)
VALUES ('2023-01-11', '2023-01-13'),
('2023-01-11', '2023-01-18'),
('2023-01-11', '2023-02-02');
如何编写一个选择语句来获得所需的输出：
-- 期望的输出
-- +──────────────+───────────────────+─────────────────────────────────+
-- | outset_date | date_of_interest | how_many_intervals_have_passed |
-- +──────────────+───────────────────+─────────────────────────────────+
-- | 2023-01-11 | 2023-01-13 | 1 |
-- | 2023-01-11 | 2023-01-18 | 2 |
-- | 2023-01-11 | 2023-02-02 | 6 |
-- +──────────────+───────────────────+─────────────────────────────────+
如果没有一个"惯用"的SQL语法，我会选择使用MySQL或PostgreSQL。谢谢！
英文:
In SQL, how can I calculate how many x-day intervals have passed since outset date?
Consider 2023-01-11 as an example for such an "outset date". For any subsequent date, I want to know how many 4-day intervals have passed since the outset date.
For example:
💚 2023-01-13 should return 1, because it's the first 4-day interval.
💙 2023-01-18 should return 2, because it's the second 4-day interval.
💜 2023-02-02 should return 6, because it's the sixth 4-day interval.
## # January 2023
## Su Mo Tu We Th Fr Sa
## -----------------------------------------------------------------------
## |1 |2 |3 |4 |5 |6 |7 |
## | | | | | | | |
## | | | | | | | |
## | | | | | | | |
## | | | | | | | |
## -----------------------------------------------------------------------
## |8 |9 |10 |11 |12 |13 |14 |
## | | | | | | | |
## | | | |*outset* | |💚 | |
## | | | |<<=======|=========|=========|=======>>|
## | | | | | | | |
## -----------------------------------------------------------------------
## |15 |16 |17 |18 |19 |20 |21 |
## | | | | | | | |
## | | | |💙 | | | |
## |<<+++++++|+++++++++|+++++++++|+++++++>>|<<@@@@@@@|@@@@@@@@@|@@@@@@@@@|
## | | | | | | | |
## -----------------------------------------------------------------------
## |22 |23 |24 |25 |26 |27 |28 |
## | | | | | | | |
## | | | | | | | |
## |@@@@@@@>>|<<#######|#########|#########|#######>>|<<*******|*********|
## | | | | | | | |
## -----------------------------------------------------------------------
## |29 |30 |31 |1 |2 |3 |4 |
## | | | | | | | |
## | | | | |💜 | | |
## |*********|*******>>|<<~~~~~~~|~~~~~~~~~|~~~~~~~~~|~~~~~~~>>| |
## | | | | | | | |
## -----------------------------------------------------------------------
So if I have the corresponding SQL table:
CREATE TABLE my_tbl (outset_date DATE, date_of_interest DATE);
INSERT INTO my_tbl (outset_date, date_of_interest)
VALUES ('2023-01-11', '2023-01-13'),
('2023-01-11', '2023-01-18'),
('2023-01-11', '2023-02-02');
How can I write a select statement to get the desired output:
-- desired output
-- +──────────────+───────────────────+─────────────────────────────────+
-- | outset_date | date_of_interest | how_many_intervals_have_passed |
-- +──────────────+───────────────────+─────────────────────────────────+
-- | 2023-01-11 | 2023-01-13 | 1 |
-- | 2023-01-11 | 2023-01-18 | 2 |
-- | 2023-01-11 | 2023-02-02 | 6 |
-- +──────────────+───────────────────+─────────────────────────────────+
If there isn't an "idimoatic" SQL syntax for this, I'd opt for either MySQL or PostgreSQL. Thanks!
答案1
得分: 1
以下是已翻译的内容：
MySQL的基本解决方案：
SELECT
outset_date,
date_of_interest,
CEIL(DATEDIFF(date_of_interest, outset_date) / 4) how_many_intervals_have_passed
FROM my_tbl;
PostgreSQL的解决方案如下：
SELECT
outset_date,
date_of_interest,
CEIL((date_of_interest - outset_date)::numeric / 4) how_many_intervals_have_passed
FROM my_tbl;
英文:
The basic solution for MySQL:
SELECT
outset_date,
date_of_interest,
CEIL(DATEDIFF(date_of_interest, outset_date) / 4) how_many_intervals_have_passed
FROM my_tbl;
PostgreSQL solution below:
SELECT
outset_date,
date_of_interest,
CEIL((date_of_interest - outset_date)::numeric / 4) how_many_intervals_have_passed
FROM my_tbl;
答案2
得分: 1
为了计算两个日期之间的天数差异，您需要从较早的日期中减去较早的日期，即"2023-01-13" - "2023-01-11" = 2
在您的情况下，您需要获取两个日期之间的天数，包括第一个和最后一个日期，这意味着您需要将天数差异加1，即"2023-01-13" - "2023-01-11" + 1 = 3
要获取日期所在的4天间隔，只需将计算出的日期差异加3，然后执行整数除法除以4，即对于差异(1, 2, 3, 4)，它将分别为(4/4, 5/4, 6/4, 7/4)，这对于所有差异都等于1。
对于Postgres，请尝试以下操作：
select *,
(date_of_interest - outset_date + 4) / 4 as expected
from my_tbl
这里的+ 4表示在上述提到的基础上加1来计算两个日期之间的差异，并加3来执行整数除法。
对于MySQL，可以使用(datediff(date_of_interest, outset_date) + 4) div 4，其中div运算符用于执行整数除法。
英文:
To count the difference between two dates in days, you need to subtract the oldest date from the earliest one, i.e. "2023-01-13" - "2023-01-11" = 2
In your case, you need the number of days between the two dates including the first and last dates, this means you need to add 1 day to the difference in days, i.e. "2023-01-13" - "2023-01-11" + 1 = 3
To get the 4 days interval in which a date lies, simply add 3 to the calculated date difference then perform integer division by 4. i.e. for differences (1, 2, 3, 4) it will be (4/4, 5/4, 6/4, 7/4) which equals to 1 for all.
For Postgres try the following:
select *,
(date_of_interest - outset_date + 4) / 4 as expected
from my_tbl
The + 4 here is +1 to calculate the difference between the two dates inclusively as mentioned above, and +3 to perform the integer division.
For MySQL, it will be (datediff(date_of_interest, outset_date) + 4) div 4, where div operator is used to perform the integer division.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论