背景我得到的数据相当直接。多个客户端,每个客户端可以有多个帐户,每个帐户可以包含多个产品(CUSIP)。我在每个工作日都会捕捉到这些数据,这意味着没有周末或任何公共假日的记录。
Client Account Product Total_value Date
10 36 '29764T101' 3240 '2018-10-01'
10 35 'TRZ300' 29761.93 '2018-10-02'
10 36 '29764T101' 3720 '2018-10-02'
10 35 'TRZ300' 29761.93 '2018-10-03'
10 36 '29764T101' 3240 '2018-10-03'
10 35 'TRZ300' 30282.76 '2018-10-04'
10 36 '29764T101' 3300 '2018-10-04'
10 35 'TRZ300' 30282.76 '2018-10-05'
10 36 '29764T101' 3300 '2018-10-05'
10 35 'TRZ300' 30282.76 '2018-10-08'
10 36 '29764T101' 3300 '2018-10-08'
10 35 'TRZ300' 30282.76 '2018-10-09'
10 36 '29764T101' 3060 '2018-10-09'
问题陈述我需要一种方法来插入每月时间序列数据中缺失天数的记录。缺少的数据来自最后一个可用的数据行。所以星期五的记录会在星期六和星期天重复出现。如果星期三不工作,星期二的记录将被填充到星期三,以此类推。
以上数据中,没有10月6日和10月7日的数据。由于这个客户机(10)有两个帐户(35,36),每个帐户只有一个产品(35->'trz300'和36->'29764t101'),我需要一种方法插入第6个的两个记录(第5个的两个记录的副本)和第6个要重复的相同内容。
请注意,我的数据位于客户机/帐户/产品/日期级别,需要查找和复制此组合。上面的示例数据仅适用于一个客户机。实际数据有多个客户端,有多个帐户,每个帐户可以有多个产品(CUSIP)。
预期产量
Client Account Product Total_value Date
10 36 '29764T101' 3240 '2018-10-01'
10 35 'TRZ300' 29761.93 '2018-10-02'
10 36 '29764T101' 3720 '2018-10-02'
10 35 'TRZ300' 29761.93 '2018-10-03'
10 36 '29764T101' 3240 '2018-10-03'
10 35 'TRZ300' 30282.76 '2018-10-04'
10 36 '29764T101' 3300 '2018-10-04'
10 35 'TRZ300' 30282.76 '2018-10-05'
10 36 '29764T101' 3300 '2018-10-05'
------------------------ Inserted Records ---------------------
10 35 'TRZ300' 30282.76 '2018-10-06'
10 36 '29764T101' 3300 '2018-10-06'
10 35 'TRZ300' 30282.76 '2018-10-07'
10 36 '29764T101' 3300 '2018-10-07'
------------------------ Inserted Records ---------------------
10 35 'TRZ300' 30282.76 '2018-10-08'
10 36 '29764T101' 3300 '2018-10-08'
10 35 'TRZ300' 30282.76 '2018-10-09'
10 36 '29764T101' 3060 '2018-10-09'
到目前为止,我了解到一种方法可能是创建一个日历表,然后执行左联接。
尝试1通过创建一个日历表,然后使用以下查询,我得到了有效的解决方案:
SELECT
CASE WHEN ID IS NULL THEN (SELECT ID
FROM T tt
WHERE tt.Date < t1.minDt
ORDER BY tt.Date DESC
LIMIT 1)
ELSE ID END ID,
CASE WHEN Name IS NULL THEN (SELECT Name
FROM T tt
WHERE tt.Date < t1.minDt
ORDER BY tt.Date DESC
LIMIT 1)
ELSE Name END Name,
CASE WHEN SomeVal IS NULL THEN (SELECT SomeVal
FROM T tt
WHERE tt.Date < t1.minDt
ORDER BY tt.Date DESC
LIMIT 1)
ELSE SomeVal END SomeVal,
CASE WHEN OtherVal IS NULL THEN (SELECT OtherVal
FROM T tt
WHERE tt.Date < t1.minDt
ORDER BY tt.Date DESC
LIMIT 1)
ELSE OtherVal END OtherVal,
minDt
FROM calendar t1
LEFT JOIN T t2 ON t1.minDt = t2.Date
ORDER BY t1.minDT;
当id值为常量时,此解决方案起作用。我意识到我的数据集有数千条记录,其中只有几百个唯一的id值。每个id都可能缺少值。上面的查询只替换数据的顶部,而不是整个数据。我需要为每个id运行相同的查询。我猜partitionby在mysql中可以工作,但我不太确定如何尝试。
尝试2这是@nick的推荐。我不完全遵循它,但它对我的虚拟数据,但只有一个层次的工作。我不太确定如何将这个解决方案扩展到一个多层次的数据,就像我上面提到的那样。
SELECT thedate,
@name := coalesce(Name, @name) AS Name,
@someval := coalesce(SomeVal, @someval) AS SomeVal,
@otherval := coalesce(OtherVal, @otherval) AS OtherVal,
@id := id AS id
FROM (SELECT c.thedate, i.id, t.Name, t.SomeVal, t.OtherVal
FROM calendar c
JOIN (SELECT DISTINCT id FROM t) i
LEFT JOIN t ON t.date = c.thedate AND t.id = i.id) g
CROSS JOIN (SELECT @id := 0, @name := '', @someval := 0, @otherval := 0) v
ORDER BY id, thedate
db fiddle示例数据我已经创建了一个虚拟数据,供任何人在https://www.db-fiddle.com/f/wzg4mytbdtejgrghfqj75k/1
示例数据和日历数据。
暂无答案!
目前还没有任何答案,快来回答吧!