B*****g
发帖数: 34098 | 1 【 以下文字转载自 JobHunting 讨论区 】
发信人: txaggie (txaggie), 信区: JobHunting
标 题: sql的2个问题
发信站: BBS 未名空间站 (Mon Sep 9 20:07:26 2013, 美东)
我知道怎么做了，刚在车上想了半天，想出来了
————————————————————
我有一个table，3个column
acct_number, Status, Day
sample table data
acct1 A 1
acct1 B 5
acct1 C 8
假如说我一共有10天，我想列出这个acct每一天的status， 如果当天没有更新的
status，那就用前一次的status，所以最后的结果应该是这样的，
acct1 A 1
acct1 A 2
acct1 A 3
acct1 A 4
acct1 B 5
acct1 B 6
acct1 B 7
acct1 C 8
acct1 C 9
acct1 C 10
那位大侠指点一二 | A*******n
发帖数: 625 | | A*******n
发帖数: 625 | | A*******n
发帖数: 625 | 4 90%以上的数据库版SQL问题可以用partition by解决 | s**********o
发帖数: 14359 | 5 挑战北京？LOL
【在 A*******n 的大作中提到】
: 90%以上的数据库版SQL问题可以用partition by解决
| p***c
发帖数: 5202 | 6 笨办法，来个 while loop
以前我解决过类似的问题，用outer apply搞定的，具体怎么做的tnnd忘掉了
楼上难道是一个语句解决的？ | B*****g
发帖数: 34098 | 7 【 以下文字转载自 JobHunting 讨论区 】
发信人: txaggie (txaggie), 信区: JobHunting
标 题: sql的2个问题
发信站: BBS 未名空间站 (Mon Sep 9 20:07:26 2013, 美东)
我知道怎么做了，刚在车上想了半天，想出来了
————————————————————
我有一个table，3个column
acct_number, Status, Day
sample table data
acct1 A 1
acct1 B 5
acct1 C 8
假如说我一共有10天，我想列出这个acct每一天的status， 如果当天没有更新的
status，那就用前一次的status，所以最后的结果应该是这样的，
acct1 A 1
acct1 A 2
acct1 A 3
acct1 A 4
acct1 B 5
acct1 B 6
acct1 B 7
acct1 C 8
acct1 C 9
acct1 C 10
那位大侠指点一二 | A*******n
发帖数: 625 | | A*******n
发帖数: 625 | | A*******n
发帖数: 625 | 10 90%以上的数据库版SQL问题可以用partition by解决 | | | s**********o
发帖数: 14359 | 11 挑战北京？LOL
【在 A*******n 的大作中提到】
: 90%以上的数据库版SQL问题可以用partition by解决
| p***c
发帖数: 5202 | 12 笨办法，来个 while loop
以前我解决过类似的问题，用outer apply搞定的，具体怎么做的tnnd忘掉了
楼上难道是一个语句解决的？ | i*****w
发帖数: 75 | 13 The following query should be able to handle the situation that the status
changes back to previous one, for example, see acct2 below, the status
changes back to B on day 8.
No Partition By ( Sorry Beijing :)
CREATE TABLE #tbl (acct varchar(10), Stat varchar(20), NoOfDay int)
INSERT INTO #tbl(acct, stat, NoOfDay)
SELECT 'Acct1', 'A', 1
UNION
SELECT 'Acct1', 'B', 5
UNION
SELECT 'Acct1', 'C', 8
UNION
SELECT 'Acct1', 'D', 9
UNION
SELECT 'Acct2', 'A', 2
UNION
SELECT 'Acct2', 'B', 3
UNION
SELECT 'Acct2', 'F', 6
UNION
SELECT 'Acct2', 'B', 8
CREATE TABLE #tblDate (dt int)
INSERT INTO #tblDate(dt)
select 1 UNION select 2 UNION select 3 UNION select 4 UNION select 5 UNION
select 6
UNION select 7 UNION select 8 UNION select 9 UNION select 10
SELECT * FROM #tbl
SELECT * INTO #tmp from (
SELECT acct, MIN(NoOfDay) minDay From #tbl group by acct
) a
CROSS JOIN #tblDate
SELECT a.acct, a.dt, g.Stat
FROM (
Select a.acct, a.dt,
MAX(CASE WHEN a.dt >= b.NoOfDay THEN b.NoOfDay ELSE c.NoOfDay END) ss
FROM #tmp a
LEFT JOIN #tbl b on a.acct = b.acct
LEFT JOIN #tbl c on a.acct = c.acct and a.minDay = c.NoOfDay
GROUP BY a.acct, a.dt
) a
LEFT JOIN #tbl g ON a.acct =g.acct and a.ss = g.NoOfDay
ORDER BY a.acct, a.dt, g.Stat | p********n
发帖数: 11 | 14 Here is another solution using T-SQL:
declare @acct as table (AcctNbr smallint, DayNbr smallint, Status char(1))
insert @acct values
(1, 3, 'A'),(1, 6, 'B'), (1, 11, 'C'), (1, 15, 'D'),
(2, 2, 'A'),(2, 7, 'B'), (2, 13, 'C'), (2, 18, 'D')
--select * from @acct
;with Boundary as (
select MIN(DayNbr) min_, MAX(DayNbr) max_ from @acct
),
AcctBoundary as (
select AcctNbr, MIN(DayNbr) min_, MAX(DayNbr) max_ from @acct group by
AcctNbr
),
tally AS (
SELECT cast(min_ as smallint) as n from Boundary
UNION ALL
SELECT cast(n + 1 as smallint) FROM tally WHERE n < (select max_ from
Boundary)
)
select b.AcctNbr, t.n as NbrOfDay, a.Status
from AcctBoundary b join Tally t on b.min_<= t.n and b.max_ >= t.n
join @acct a
on b.AcctNbr = a.AcctNbr
and a.DayNbr = (select max(DayNbr) from @acct where AcctNbr = b.
AcctNbr and DayNbr <= t.n)
order by b.AcctNbr, t.n, a.DayNbr | l******b
发帖数: 39 | 15 个人认为解决此问题最佳方法window partitioning + cross apply
T-SQL 2012 提供CROSS APPLY比Oracle 11.2(不提供CROSS APPLY)稍胜一筹.
T-SQL method
---------------------------------
CREATE TABLE t9(
acct VARCHAR(20),
status VARCHAR(20),
nDay int
);
INSERT INTO t9(acct,status, nDay) VALUES('Acct1', 'A', 1) ;
INSERT INTO t9(acct,status, nDay) VALUES('Acct1', 'B', 5) ;
INSERT INTO t9(acct,status, nDay) VALUES('Acct1', 'C', 8) ;
INSERT INTO t9(acct,status, nDay) VALUES('Acct1', 'D', 9) ;
INSERT INTO t9(acct,status, nDay) VALUES('Acct2', 'A', 2) ;
INSERT INTO t9(acct,status, nDay) VALUES('Acct2', 'B', 3) ;
INSERT INTO t9(acct,status, nDay) VALUES('Acct2', 'F', 6) ;
INSERT INTO t9(acct,status, nDay) VALUES('Acct2', 'B', 8) ;
select * from t9 ;
----------------------------------------------------------
acct status nDay
Acct1 A 1
Acct1 B 5
Acct1 C 8
Acct1 D 9
Acct2 A 2
Acct2 B 3
Acct2 F 6
Acct2 B 8
CROSS APPLY可让你的SQL更为简洁, 并针对执行路径进行过优化, 首选方案.
WITH
C AS(
SELECT acct, status, nDay,
isnull(lead(nday) OVER(PARTITION BY acct ORDER BY nday) - nDay,
11 - nDay) AS mDay
FROM t9
)
SELECT acct, status, B.nD
FROM C as A CROSS apply
(SELECT nD FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),
(10)) AS Num(nD)) as B
where B.nD BETWEEN A.nDay AND A.nDay + A.Mday - 1 ;
---------------------------------------------------------------
acct status nD
Acct1 A 1
Acct1 A 2
Acct1 A 3
Acct1 A 4
Acct1 B 5
Acct1 B 6
Acct1 B 7
Acct1 C 8
Acct1 D 9
Acct1 D 10
Acct2 A 2
Acct2 B 3
Acct2 B 4
Acct2 B 5
Acct2 F 6
Acct2 F 7
Acct2 B 8
Acct2 B 9
Acct2 B 10 | l******b
发帖数: 39 | 16 The good news is CROSS APPLY is available since Oracle 12.1 | l******b
发帖数: 39 | 17 Workaround for Oracle earlier than 12.1
CREATE OR REPLACE TYPE int_row_table AS TABLE OF NUMBER;
/
-- Table Function is actually a parameter view.
CREATE OR REPLACE FUNCTION get_int_table
(
p_start IN NUMBER,
p_num in number
) RETURN int_row_table IS
l_result int_row_table ;
CURSOR C IS
WITH KK AS
(SELECT ROWNUM AS nD FROM dual CONNECT BY LEVEL <=10)
SELECT nD FROM KK WHERE nD BETWEEN p_start AND p_start + p_num
- 1;
BEGIN
OPEN C;
FETCH C BULK COLLECT INTO l_result;
CLOSE C;
RETURN l_result;
END get_int_table ;
/
--Lateral Join
WITH
C AS(
SELECT acct, status, nDay,
nvl(lead(nday) OVER(PARTITION BY acct ORDER BY nday) - nDay,
11 - nDay) AS mDay
FROM t9
) SELECT acct, status, inner.*
FROM C OUTER CROSS JOIN table(get_int_table(outer.nDay,outer.mDay)) INNER ;
----------------------------------------------
ACCT STATUS COLUMN_VALUE
Acct1 A 1
Acct1 A 2
Acct1 A 3
Acct1 A 4
Acct1 B 5
Acct1 B 6
Acct1 B 7
Acct1 C 8
Acct1 D 9
Acct1 D 10
Acct2 A 2
Acct2 B 3
Acct2 B 4
Acct2 B 5
Acct2 F 6
Acct2 F 7
Acct2 B 8
Acct2 B 9
Acct2 B 10 | s**********o
发帖数: 14359 | 18 为什么不是
acct status nD
Acct1 A 1
Acct1 A 1.1
Acct1 A 1.2
Acct1 A 1.3
Acct1 A 1.4
Acct1 A 1.5
。。
这个问题显然做很多假设在里面，没有什么实际意义 | d****n
发帖数: 12461 | 19 Oracle写法
with t0 as
(select
acct_number,status,day,
row_number() over (partition by acct_number order by day) rn
from t)
select
t3.acct_number,t3.status,t3.day+t4.level
from
(select
t1.acct_number,t1.status,t1.day,
nvl(t2.day,trunc(sysdate)+1)-t1.day day_between
from
t0 t1,
t0 t2
where
t1.acct_number=t2.acct_number(+)
and t1.rn+1=t2.rn(+)
) t3
(select level-1 day_delta from dual connect by level<36500) t4
where
t4.day_delta
【在 B*****g 的大作中提到】
: 【 以下文字转载自 JobHunting 讨论区 】
: 发信人: txaggie (txaggie), 信区: JobHunting
: 标 题: sql的2个问题
: 发信站: BBS 未名空间站 (Mon Sep 9 20:07:26 2013, 美东)
: 我知道怎么做了，刚在车上想了半天，想出来了
: ————————————————————
: 我有一个table，3个column
: acct_number, Status, Day
: sample table data
: acct1 A 1
|
|
