从开始和结束日期开始活跃的几周 [英] Get active weeks from start and end date
问题描述
我有如下订阅数据.数据显示用户何时购买了订阅.它具有 user_id,subscription_id,开始日期和结束日期
.
I have subscriptions data as shown below. The data shows when a user bought a subscription. It has user_id,subscription_id,start date and end_date
.
我从中派生了 wk_start
和 wk_end
.
+------+-----------------+------------+------------+----------+--------+
| user | subscription_id | start | end | wk_start | wk_end |
+------+-----------------+------------+------------+----------+--------+
| 1 | 1A | 2019-06-01 | 2019-06-30 | 22 | 27 |
| 2 | 2A | 2019-06-01 | 2019-06-21 | 22 | 25 |
| 3 | 3A | 2019-06-01 | 2019-06-21 | 22 | 24 |
| 1 | 1B | 2019-07-04 | 2019-08-04 | 27 | 29 |
| 2 | 2B | 2019-07-01 | 2019-07-31 | 27 | 31 |
+------+-----------------+------------+------------+----------+--------+
现在,我想以一种方式转换数据,以便对于每个 user,subscription_id
,我将获得特定用户的活动周数.
Now I want to transform the data in a such a way that for each user,subscription_id
, I will get the active weeks for a particular user.
所需的输出如下
+------+-----------------+------+
| user | subscription_id | Week |
+------+-----------------+------+
| 1 | 1A | 22 |
| 1 | 1A | 23 |
| 1 | 1A | 24 |
| 1 | 1A | 25 |
| 1 | 1A | 26 |
| 1 | 1A | 27 |
| 2 | 2A | 22 |
| 2 | 2A | 23 |
| 2 | 2A | 24 |
| 2 | 2A | 25 |
| 3 | 3A | 22 |
| 3 | 3A | 23 |
| 3 | 3A | 24 |
| 1 | 1B | 27 |
| 1 | 1B | 28 |
| 1 | 1B | 29 |
| 2 | 2B | 27 |
| 2 | 2B | 28 |
| 2 | 2B | 29 |
| 2 | 2B | 30 |
| 2 | 2B | 31 |
+------+-----------------+------+
它基本上扩展了订阅数据,使每个用户的活动周有效.
It basically expands the subscriptions data to get active weeks for each user.
例如,具有 subscription_id 1A
的用户1
在22周至27周处于活动状态.
For example user 1
with subscription_id 1A
was active from week 22 till 27.
具有 subscription_id 2A
的用户2
在第22周到第25周一直处于活动状态.
Similarly user 2
with subscription_id 2A
was active from week 22 till 25.
现在,用户2
从27周到31年再次处于活动状态,并且 subscription_id 2B
Now user 2
was again active from week 27 till 31 with subscription_id 2B
我希望这有助于了解预期的结果
I hope this helps to understand the desired result
我在这里制作了 db-fiddle .(尽管它在mysql),但应该以其他方式提供帮助
I have made a db-fiddle here.(though it's in mysql) but should help in some or the other way
推荐答案
您可以创建一个包含所有星期数的表,并对它进行部分笛卡尔连接,如下所示:
You can create a table that includes all week numbers and do a partial cartesian join against it, something like this:
CREATE TABLE weeks (
`week` INTEGER
);
INSERT INTO weeks
(`week`)
VALUES
(1), (2), (3), (4), (5), (6), (7), (8), (9)
, (10), (11), (12), (13), (14), (15), (16), (17), (18), (19)
, (20), (21), (22), (23), (24), (25), (26), (27), (28), (29)
, (30), (31), (32), (33), (34), (35), (36), (37), (38), (39)
, (40), (41), (42), (43), (44), (45), (46), (47), (48), (49)
, (50), (51), (52), (53)
;
SELECT t1.user, t1.subscription_id, w.week
FROM table1 t1
CROSS JOIN weeks w
WHERE w.week BETWEEN t1.wk_start and t1.wk_end
ORDER BY t1.user, t1.subscription_id, w.week;
https://www.db-fiddle.com/f/mn4towXxscmLsibQjwtNEH/0
周"表似乎有点麻烦,但只需创建一次即可,然后您就可以忘记它了.
The weeks table seems like a bit of a nuisance, but it just has to be created once and then you can forget about it.
这篇关于从开始和结束日期开始活跃的几周的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!