i*******d
发帖数: 81 | 1 如下的表一，简单起见，日期用数字代替了。
做active member count的时候，要避免double count。即在任何一天，一个member不
能被count一次以上。
那么表一需要处理成表二的形式，即要把overlap的时间去掉而同时不引入其他
的错误。
请教如何用SQL实现。多谢！
Table 1(original)：
member from_date to_date
A 1 2
A 2 3
A 2 4
B 1 2
B 5 7
B 6 8
Change to:
Table 2(needed)：
member from_date to_date
A 1 4
B 1 2
B 5 8 |
B*****g
发帖数: 34098 | 2 这个看来不是家庭作业，记不得以前有人做过没？俺现在没时间，要出去腐败
一点想法，要用LAG and LEAD, go partition by!!!
【在 i*******d 的大作中提到】
: 如下的表一，简单起见，日期用数字代替了。
: 做active member count的时候，要避免double count。即在任何一天，一个member不
: 能被count一次以上。
: 那么表一需要处理成表二的形式，即要把overlap的时间去掉而同时不引入其他
: 的错误。
: 请教如何用SQL实现。多谢！
: Table 1(original)：
: member from_date to_date
: A 1 2
: A 2 3
|
e****7
发帖数: 4387 | |
B*****g
发帖数: 34098 | 4 SQL，我看看recursive sql能不能搞
【在 e****7 的大作中提到】
: 我想可能要loop 或者cursor
|
e****7
发帖数: 4387 | 5
嗯，先试试，我直觉是先做个time table, join 一下，帮助找出所有unique days, 然
后再tsql 里用while loop, 或coursor 神马的，比较直接，不用费脑，recursive 或
许有点费劲不，不过应该比较efficient 。
【在 B*****g 的大作中提到】
: SQL，我看看recursive sql能不能搞
|
i*******d
发帖数: 81 | 6 多谢。
recursive应该可以。没想起来用。
最好用ANSI syntax来解。 是SQL Server 2008 R2。 Oracle中的connect by不能用。
【在 B*****g 的大作中提到】
: SQL，我看看recursive sql能不能搞
|
B*****g
发帖数: 34098 | 7 抄了一个，嘿嘿
WITH
sorted_requests as (
SELECT
member, from_date, to_date,
ROW_NUMBER() OVER (PARTITION BY member ORDER BY from_date, to_date
DESC) Instance
FROM
TABLE1
),
no_overlap(member, from_date, to_date, Instance, ConnectedGroup) as (
SELECT
member,
from_date,
to_date,
Instance,
Instance as ConnectedGroup
FROM sorted_requests
WHERE Instance = 1
UNION ALL
SELECT
s.member,
s.from_date,
CASE WHEN n.to_date >= s.to_date
THEN n.to_date
ELSE s.to_date
END to_date,
s.Instance,
CASE WHEN n.to_date >= s.from_date
THEN n.ConnectedGroup
ELSE s.Instance
END ConnectedGroup
FROM sorted_requests s
INNER JOIN no_overlap n
ON s.member = n.member AND s.Instance = n.Instance + 1
)
SELECT
member,
MIN(from_date) from_date,
MAX(to_date) to_date
FROM no_overlap
GROUP BY member, ConnectedGroup
ORDER BY member
【在 i*******d 的大作中提到】
: 多谢。
: recursive应该可以。没想起来用。
: 最好用ANSI syntax来解。 是SQL Server 2008 R2。 Oracle中的connect by不能用。
|
i*******d
发帖数: 81 | 8 不是recursive？
【在 B*****g 的大作中提到】
: 抄了一个，嘿嘿
: WITH
: sorted_requests as (
: SELECT
: member, from_date, to_date,
: ROW_NUMBER() OVER (PARTITION BY member ORDER BY from_date, to_date
: DESC) Instance
: FROM
: TABLE1
: ),
|
B*****g
发帖数: 34098 | 9 怎么不是？
【在 i*******d 的大作中提到】
: 不是recursive？
|
i*******d
发帖数: 81 | 10 是。
【在 B*****g 的大作中提到】
: 怎么不是？
|
|
|
B*****g
发帖数: 34098 | 11 你们做membership的？
【在 i*******d 的大作中提到】
: 是。
|
i*******d
发帖数: 81 | 12 不是。make up 的一个 scenario。
【在 B*****g 的大作中提到】
: 你们做membership的？
|
B*****g
发帖数: 34098 | 13 自学？
【在 i*******d 的大作中提到】
: 不是。make up 的一个 scenario。
|
e****7
发帖数: 4387 | 14
真不错，CTE做recursion .
【在 B*****g 的大作中提到】
: 抄了一个，嘿嘿
: WITH
: sorted_requests as (
: SELECT
: member, from_date, to_date,
: ROW_NUMBER() OVER (PARTITION BY member ORDER BY from_date, to_date
: DESC) Instance
: FROM
: TABLE1
: ),
|
i*******d
发帖数: 81 | 15 恩。
实际的问题用min()， max()， row_number() over partition by 就能解决了。
然后想到了这个更复杂些的case，觉得挺有意思，又不会。从来没用过recursive
query。
【在 B*****g 的大作中提到】
: 自学？
|
B*****g
发帖数: 34098 | 16 复杂的sql基本上就是自娱自乐，嘿嘿
【在 i*******d 的大作中提到】
: 恩。
: 实际的问题用min()， max()， row_number() over partition by 就能解决了。
: 然后想到了这个更复杂些的case，觉得挺有意思，又不会。从来没用过recursive
: query。
|
i*******d
发帖数: 81 | 17 这个挺有用。
能remove dates之间的overlap，同时maintain gap。
以后遇到需要这样merge rows的情况，就有现成的solution了。
【在 B*****g 的大作中提到】
: 复杂的sql基本上就是自娱自乐，嘿嘿
|
