根据快照日期和临时表中的ID将记录更新/插入到不同的快照表中

问题描述：

我有一个巨大的快照表（示例user_snapshot_all）分解为Redshift上不同的较小表（即Postgres）以获得性能增益。根据快照日期和临时表中的ID将记录更新/插入到不同的快照表中

所以，较小的表像（后缀有YEAR_MONTH）

user_snapshot_1995_1 
user_snapshot_1995_2 
user_snapshot_1995_3 
user_snapshot_1995_4 
.... 
user_snapshot_2016_11

他们认为他们拥有的任何年份和月份后缀快照记录

我用一个临时表user_snapshot_staging加载/更新数据在99％的情况下，这些表格逐渐增加，这只是最新的一个月。

但会有一些边缘情况下，像午夜12时临时表将具有将跨越两个表中的数据（例如user_snaspshot_2016_10和user_snapshot_2016_11于2016年11月1日）或者另一边的情况下，也许我们需要更新几年2年的快照，因此临时表将会有一些2年的记录以及今天的许多快照。

问题是如何设计我的查询或代码，以便它可以更新或插入数据到正确的month_year快照表？

所有快照表和登台表至少有这两个栏目：

id 
snapshot_date

为了进一步澄清： 如果是单user_snapshot_all我可以很容易地通过主加入临时表更新记录表基于snapshot_date和id。但是用这些较小的表按月份分段，不能保证可以在一个快照表中找到登台表中的所有记录。

下面是用例 注：下面的查询是一个ETL过程的一部分，他们不是一次性的手动之一，这就是为什么我需要自动解决方案。

情景1）假设user_snapshot_staging表有

id snapshot_date user_detail 
100 2016-11-3  jskesljd234 
101 2016-11-4  jskesljdfg23 
102 2016-11-5  jskesljdbd23 
103 2016-11-6  jskesljdw23ds

，因为所有的快照属于2016年11月，所有这些数据将被插入/更新到user_snapshot_2016_11与以下两个查询：

插入新：

Insert into user_info_snapshot_2011_11 (id, snapshot_date, user_detail) 
from user_info_snapshot_staging source LEFT OUTER JOIN user_info_snapshot_2011_11 target on source.id = target.id where target.id is null 
;

UPD吃了现有：

update user_info_snapshot_2011_11 set snapshot_date=source.snapshot_date, user_detail=source.user_detail 
from user_info_snapshot_staging source INNER JOIN user_info_snapshot_2011_11 target on source.id = target.id where

方案2）现在假设user_snapshot_staging表有

id snapshot_date user_detail 
1300 2015-01-3  jskesljd234 
1301 2015-10-4  jskesljdfg23 
1302 2016-11-1  jskesljdbd23 
1303 2016-11-2  jskesljdw23ds

现在临时表具有快照，这将需要更新和插入到不同的快照表中，我们不能只是插入/更新成user_snapshot_2016_11，但我们需要也插入/更新到user_snapshot_2015_01和user_snapshot_2015_10

应该怎么样我设计了我的查询或代码来生成动态查询来处理这些情况，以便只有基于临时表中的数据才将相应的表与基于user_snapshot_staging的表连接起来？

让我知道你是否需要进一步的澄清。对不起，解释有点棘手。

为什么这个问题标记为[oracle]？ – APC

你的问题是细节，但它很混乱。所以请提供样本预期结果 – Viki888

@APC，因为您也可以提供oracle更新查询 – Watt

答

您可以使用以下方法生成查询。我将给出基于python语法的伪代码示例。你有

获取的年/月组合中的临时数据库


SELECT DISTINCT to_char(date, 'YYYY-MM') FROM user_info_snapshot_staging;

这是您的查询模板：

-- insert_template.sql 
INSERT INTO user_info_snapshot_{{ year }}_{{ month }} (id, snapshot_date, user_detail) 
FROM user_info_snapshot_staging source LEFT OUTER JOIN user_info_snapshot_{{ year }}_{{ month }} target on source.id = target.id where target.id is null 
WHERE DATE_TRUNC('month', source.date) = {{ month }} AND DATE_TRUNC('year', source.date) = {{ year }}; 

-- update_template.sql 
UPDATE user_info_snapshot_{{ year }}_{{ month }} SET snapshot_date=source.snapshot_date, user_detail=source.user_detail 
FROM user_info_snapshot_staging source INNER JOIN user_info_snapshot_{{ year }}_{{ month }} target on source.id = target.id where 
DATE_TRUNC('month', source.date) = {{ month }} AND DATE_TRUNC('year', source.date) = {{ year }};

现在循环遍历年/月对并执行这些查询：

for year_month, in cursor.execute("SELECT to_char('YYYY-MM', date_columns) FROM user_info_snapshot_staging"): 
    year, month = year_month.split('-') 
    # this is where you generate sql 
    sql = template('insert_template', context={ 
     'year': year, 
     'month': month, 
    }) 
    # here you execute it 
    cursor.execute(sql)

我建议不要使用update如果你需要更新大量的记录。进一步的信息在this question。

开始=>

根据快照日期和临时表中的ID将记录更新/插入到不同的快照表中

相关推荐