For example, when fundinnercode = 4082, datestamp = 2010-06-30 (highlighted in the picture), unit_nav, returnrate and value are all null. but this row is present in the table t_fund_nav_ss as shown below:
what is even weirder is when I use the where clause to specifically locate the row in the end results, the three missing columns appear to have data in them
SELECT T1.INNERCODE AS CODE,
T2.DATESTAMP,
T1.REPORTDATE,
t1.FUNDINNERCODE,
T1.MARKETVALUE,
T1.SHARESHOLDING,
T1.RATIOINNV,
T3.UNIT_NAV,
T3.VALUE,
T3.RETURNRATE
FROM JUYUAN_DB.MF_FUNDPORTIFOLIODETAIL T1
LEFT JOIN (SELECT CALENDAR_DATE AS CALENDAR_DATE,
CLOSEST_DATETIME_BEFORE AS DATESTAMP
FROM APP_RQA.T_CALENDAR_SS
WHERE EXCHMARKET = '83') T2
ON T1.REPORTDATE = T2.CALENDAR_DATE
LEFT JOIN (SELECT CODE,
DATESTAMP,
UNIT_NAV,
VALUE,
RETURNRATE
FROM APP_RQA.T_FUND_NAV_SS) T3
ON T1.FUNDINNERCODE = T3.CODE
AND T2.DATESTAMP = T3.DATESTAMP
WHERE T1.FUNDINNERCODE = 4082
AND T2.DATESTAMP = '2010-06-30'
I could not wrap my head around it and any help or suggestions are greatly appreciated.
uj5u.com熱心網友回復：
感謝大家的評論。我今天偶然發現了一位大資料工程師，并向他詢問了這個問題。對于那些好奇的人，原來問題在于 T3 表的大小。T1 和 T2 大約有 20-30 k 行，而 T3 有 30 百萬行。而左連接操作會導致資料丟失。所以我首先將 T3 過濾到大約 2 百萬行，結果現在看起來很正常。
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/449258.html