so you want last available status, if there is any
SELECT T.[ID], T.[EVENT_DESC], TODAY() FROM table AS T INNER JOIN ( SELECT [ID], MAX([EVENT_DATE]) AS LAST_EVENT_DATE FROM table WHERE [EVENT_DATE] < TODAY() GROUP BY [ID] ) AS LAST_EVENT_LIST ON T.[ID] = LAST_EVENT_LIST.[ID] AND T.[EVENT_DATE] = LAST_EVBENT_LIST.[LAST_EVENT_DATE]
(SELECT TO_DATE ('01-JAN-2020') + ROWNUM - 1 AS EVENT_DATE FROM (SELECT ROWNUM FROM ( SELECT 1 FROM DUAL CONNECT BY LEVEL <= (TO_DATE ('01-FEB-2020') - TO_DATE ('01-JAN-2020')))) ) A, ROBUSTSONG B
WHERE A.EVENT_DATE = B.EVENT_DATE(+) ORDER BY A.EVENT_DATE, B.ID;
可以在原table上加一个字段，叫Event_End_Date，和Event_Date一起构成一个range。方法是用analytic function: select lead(Event_Date) over (partition by ID order by Event_Date) as Event_End_Date.
查询的时候，比如你有一个as_of_date=1/10/2020，你就 select * from table where as_of_date between Event_Date and Event_End_Date-1
我现在有一个table，里面存了下面的信息
ID Event_Date Event_Desc
1 1/3/2020 Applied
1 1/5/2020 Approved
2 1/4/2020 Applied
2 1/6/2020 Denied
每个ID申请了之后，就会出现一个申请的日期，approved之后就会出现approved的日期。
但是这个table有个问题，我想选在1/4/2020有多少人applied/approved/denied.这个
是不行的
我现在想生成个带完全日期的记录。先生成一个日期 reference table
Date
1/1/2020
1/2/2020
1/3/2020
…
10/27/2020
跟上面的table join一下，变成下面的这个
Date ID Event_Desc
1/1/2020
1/2/2020
1/3/2020 1 Applied
1/4/2020 1 Applied
1/4/2020 2 Applied
1/5/2020 1 Approved
1/5/2020 2 Applied
1/6/2020 1 Approved
1/6/2020 2 Denied
1/7/2020 1 Approved
1/7/2020 2 Denied
1/8/2020 1 Approved
1/8/2020 2 Denied
…
这样子我就可以随意选任何的一天来分析当天的数据。之后还可以生成一个daily
trend chart.
请教一下，Oracle sql怎么实现这个join？谢谢
just generate a table first with all date. maybe a small loop to generate it?
you may find something online.
then use it to left join our own data table.
date list我已经生成了。我现在的难点就是如何left join生成我想要的table。
简单的left join生成的结果跟原数据table没什么区别。
先把日期和Event分开，生成两个不同的column。然后用新生成的日期来filter。
那个多少人的输出用count，可以count（ID），或者count（distinct ID）
"我想选在1/4/2020有多少人applied/approved/denied"
楼主究竟想要啥呢？
SELECT DISTINCT ID
FROM table
WHERE EVENT_DATE = '2020-01-04' AND EVENT_DESC IN ('applied', 'approved', 'denied')
不是你想要的结果
我问的是如何来做这个join。join做好了，就可以选择某一天的数据进行分析，跟你写的这个类似。
比如ID = 1的这个人， 1月3号申请了一张信用卡，他就会在1/3/2020有一个applied的记录。1/5号信用卡批准，就会在1/5/2020有一个approved的记录。但是在原始的table里面，这个人就只有这么两个记录。如果想知道1月10号这个人的状态，这就不能直接
设定日期等于1/10/2020，必须要做一个比较，小于这个日期的是怎么样的。
我现在想通过join一个date list做一个全历史的table，这样子，申请人只要申请过以后，每天都会有记录，最开始是applied，等到decision变成approved后就一直是
approved。这个数据结果可以直接做data visualization了。
现在关键的问题是如何通过join来fill那些日期。简单的left join并不能fill这些日期
【 在 PCB (越学越笨) 的大作中提到: 】
: "我想选在1/4/2020有多少人applied/approved/denied"
: 楼主究竟想要啥呢？
: SELECT DISTINCT ID
: FROM table
: WHERE EVENT_DATE = '2020-01-04' AND EVENT_DESC IN ('applied', '
approved'
: , 'denied')
: 不是你想要的结果
现在我的难点是如何在left join的时候fill那些在原始数据table里面没有记录的日期。
【 在 eagle7 (衣勾七) 的大作中提到: 】
: 先把日期和Event分开，生成两个不同的column。然后用新生成的日期来filter。
: 那个多少人的输出用count，可以count（ID），或者count（distinct ID）
so you want last available status, if there is any
SELECT T.[ID], T.[EVENT_DESC], TODAY()
FROM
table AS T
INNER JOIN
(
SELECT [ID], MAX([EVENT_DATE]) AS LAST_EVENT_DATE
FROM table
WHERE [EVENT_DATE] < TODAY()
GROUP BY [ID]
) AS LAST_EVENT_LIST ON T.[ID] = LAST_EVENT_LIST.[ID] AND T.[EVENT_DATE] =
LAST_EVBENT_LIST.[LAST_EVENT_DATE]
【 在 robustsong (莫非) 的大作中提到: 】
: 现在我的难点是如何在left join的时候fill那些在原始数据table里面没有记录的日期。
-- This script generate the date data in range:
SELECT TO_DATE ('01-JAN-2020') + ROWNUM - 1
FROM (SELECT ROWNUM
FROM ( SELECT 1
FROM DUAL
CONNECT BY LEVEL <=
(TO_DATE ('01-FEB-2020') - TO_DATE ('01-JAN-2020'))));
-- This script generate the user data :
CREATE TABLE ROBUSTSONG
(
ID INTEGER,
EVENT_DATE DATE,
EVENT_DESC VARCHAR2(100)
);
Insert into ROBUSTSONG
(ID, EVENT_DATE, EVENT_DESC)
Values
(1, TO_DATE('1/3/2020', 'MM/DD/YYYY'), 'Applied');
Insert into ROBUSTSONG
(ID, EVENT_DATE, EVENT_DESC)
Values
(1, TO_DATE('1/5/2020', 'MM/DD/YYYY'), 'Approved');
Insert into ROBUSTSONG
(ID, EVENT_DATE, EVENT_DESC)
Values
(2, TO_DATE('1/4/2020', 'MM/DD/YYYY'), 'Applied');
Insert into ROBUSTSONG
(ID, EVENT_DATE, EVENT_DESC)
Values
(2, TO_DATE('1/6/2020', 'MM/DD/YYYY'), 'Denied');
Insert into ROBUSTSONG
(ID, EVENT_DATE, EVENT_DESC)
Values
(3, TO_DATE('1/3/2020', 'MM/DD/YYYY'), 'Applied');
COMMIT;
-- This script generate the result :
SELECT A.*, B.* FROM
(SELECT TO_DATE ('01-JAN-2020') + ROWNUM - 1 AS EVENT_DATE
FROM (SELECT ROWNUM
FROM ( SELECT 1
FROM DUAL
CONNECT BY LEVEL <=
(TO_DATE ('01-FEB-2020') - TO_DATE ('01-JAN-2020')))) ) A,
ROBUSTSONG B
WHERE A.EVENT_DATE = B.EVENT_DATE(+)
ORDER BY A.EVENT_DATE, B.ID;
--out put
1/1/2020
1/2/2020
1/3/2020 1 1/3/2020 Applied
1/3/2020 3 1/3/2020 Applied
1/4/2020 2 1/4/2020 Applied
1/5/2020 1 1/5/2020 Approved
1/6/2020 2 1/6/2020 Denied
1/7/2020
1/8/2020
:
:
1/31/2020
Is this what you need?
【 在 robustsong (莫非) 的大作中提到: 】
: 我现在有一个table，里面存了下面的信息
: ID Event_Date Event_Desc
: 1 1/3/2020 Applied
: 1 1/5/2020 Approved
: 2 1/4/2020 Applied
: 2 1/6/2020 Denied
: 每个ID申请了之后，就会出现一个申请的日期，approved之后就会出现approved的日期。
: 但是这个table有个问题，我想选在1/4/2020有多少人applied/approved/denied.这个
: 是不行的
: 我现在想生成个带完全日期的记录。先生成一个日期 reference table
: ...................
听起来你想要的是任意一天所有客户状态的snapshot。这应该是一个range join的问题。
可以在原table上加一个字段，叫Event_End_Date，和Event_Date一起构成一个range。方法是用analytic function:
select lead(Event_Date) over (partition by ID order by Event_Date) as Event_End_Date.
查询的时候，比如你有一个as_of_date=1/10/2020，你就
select *
from table
where as_of_date between Event_Date and Event_End_Date-1
加了这个字段之后，那个table就相当于一个type 2 dimension table，也有人叫
factless fact table。它包含所有的历史。另外加了那个字段并不改变table key结构，primary key仍然是ID+Event_Date。
如果不能改变原table结构的话，那就外置一个table，叫range_lookup table，把ID，Event_Date，Event_End_Date放在这里。查询的时候把它join上，相当于把这个字段加到原table上了。
【 在 robustsong (莫非) 的大作中提到: 】
: 我问的是如何来做这个join。join做好了，就可以选择某一天的数据进行分析，跟你写
: 的这个类似。
: 比如ID = 1的这个人， 1月3号申请了一张信用卡，他就会在1/3/2020有一个applied的
: 记录。1/5号信用卡批准，就会在1/5/2020有一个approved的记录。但是在原始的
table
: 里面，这个人就只有这么两个记录。如果想知道1月10号这个人的状态，这就不能直接
: 设定日期等于1/10/2020，必须要做一个比较，小于这个日期的是怎么样的。
: 我现在想通过join一个date list做一个全历史的table，这样子，申请人只要申请过以
: 后，每天都会有记录，最开始是applied，等到decision变成approved后就一直是
: approved。这个数据结果可以直接做data visualization了。
: 现在关键的问题是如何通过join来fill那些日期。简单的left join并不能fill这些
日期
: ...................
你这个TABLE建的有问题,把所有的EVENT去UPDATE这个表，应该是这样的
PID AppliedFlg AppliedDt ApprovedFlg ApprovedDt DeniedFlg DeniedDt
1 1 1/4/2020 null null null null
2 1 1/5/2020 1 1/6/2020 null null
3 1 1/5/2020 null null 1 1/7/2020
他这个是个journal table。
【 在 smallburrito (smallburrito) 的大作中提到: 】
: 你这个TABLE建的有问题,把所有的EVENT去UPDATE这个表，应该是这样的
: PID AppliedFlg AppliedDt ApprovedFlg ApprovedDt DeniedFlg DeniedDt: 1 1 1/4/2020 null null null null
: 2 1 1/5/2020 1 1/6/2020 null null
: 3 1 1/5/2020 null null 1 1/7/
2020
这是每个人的STATUS TABLE，
你关心的是STATUS状态和什么时候变的，
这才是USABLE TABLE，那个EVENT TABLE
就是个LOG TABLE，没有使用价值，
因为有一个潜在的SEQUENCE存在
APPLY->APPROVE
APPLY->DENY
【 在 TheMatrix (TheMatrix) 的大作中提到: 】
: 他这个是个journal table。
: 2020
他有这么一个table，这是起始条件。接下来怎么做，这就是他来问的问题。你打算怎
么做？
【 在 smallburrito (smallburrito) 的大作中提到: 】
: 这是每个人的STATUS TABLE，
: 你关心的是STATUS状态和什么时候变的，
: 这才是USABLE TABLE，那个EVENT TABLE
: 就是个LOG TABLE，没有使用价值，
: 因为有一个潜在的SEQUENCE存在
: APPLY->APPROVE
: APPLY->DENY
填表啊，把每个人的STATUS填上去
【 在 TheMatrix (TheMatrix) 的大作中提到: 】
: 他有这么一个table，这是起始条件。接下来怎么做，这就是他来问的问题。你打算怎
: 么做？
嗯，有多种方法，能做出来就行。
【 在 smallburrito (smallburrito) 的大作中提到: 】
: 填表啊，把每个人的STATUS填上去
这种EVENT的LOG TABLE就是流水帐子,
MEANINGFUL的是个人的STATUS TABLE，
如果不想保留这个TABLE，那就在STORED PROCEDURE
BUILD TEMP TABLE，每次QUERY这个TABLE容易多了
【 在 TheMatrix (TheMatrix) 的大作中提到: 】
: 嗯，有多种方法，能做出来就行。
属实。
这种event log table，或者叫journal table，transaction table，是业务运行table。它方便写，不方便读。
但是改成每天每个ID一条status记录，会产生大量冗余，因为ID status change是很少的事件。所以改成range比较合适。
【 在 smallburrito (smallburrito) 的大作中提到: 】
: 这种EVENT的LOG TABLE就是流水帐子,
: MEANINGFUL的是个人的STATUS TABLE，
: 如果不想保留这个TABLE，那就在STORED PROCEDURE
: BUILD TEMP TABLE，每次QUERY这个TABLE容易多了
非常感谢大家的帮忙。这个问题被公司的一个DBA解决了，但是方法没告诉我。不过已
经能把活干完了。