sql - 如何使用 SQL 逐行更新使用事务日志的表
问题描述
我有一个表格,其中显示了工单 ID 及其状态以及在 BI 数据库上分配给谁。这里没有重复和粒度是每张票。
票务表
Ticket_ID Status Assigned_To Date
001 Open Team A 01/01/2020 10:01
002 Working Team B 01/01/2020 10:01
003 Open TeamC 02/01/2020 18:50
004 ResolvedTeam B 02/01/2020 19:22
现在,有一个包含每个更改的每日事务日志。但是,字段更改显示为行,如下所示。
每日交易记录
Ticket_id Field_Name New Value Date
001 Assigned_to Team B 02/01/2020 10:23
002 Assigned_to Team A 02/01/2020 10:23
002 Status Resolved 02/01/2020 10:24
003 status Resolved 03/01/2020 10:24
004 Assigned_to Team C 03/01/2020 13:50
004 Assigned_to Team A 03/01/2020 13:51
004 Status Resolved 03/01/2020 13:51
现在我想编写一个更新脚本来根据日志上的新值更新 Ticket 表。考虑到每天都会创建事务日志表,我认为 set 操作将不起作用。我需要 SQL 脚本逐行处理事务日志表并使用新值更新票证表。我知道这是低效的,但是,每天只有数百行。不是几千。
我希望看到的结果如下所示,其中票证表已更新为最新值..
Ticket_ID Status Assigned_To Date
001 Open Team B 02/01/2020 10:23
002 Resolved Team A 02/01/2020 10:24
003 Resolved TeamC 03/01/2020 10:24
004 Resolved Team A 03/01/2020 13:51
有人能帮我吗。谢谢你
解决方案
您可以使用以下命令获取每列的最新更改distinct on
:
select distinct on (ticket_id, field_name) dtl.*
from daily_transaction_logs dtl
order by ticket_id, date desc;
然后,您可以将其汇总以获得每张票的一行:
select ticket_id,
max(new_value) filter (where field_name = 'Assigned_to') as assigned_to,
max(new_value) filter (where field_name = 'Status') as status,
max(date) as date
from (select distinct on (ticket_id, field_name) dtl.*
from daily_transaction_logs dtl
order by ticket_id, date desc
) tf
group by ticket_id;
最后,您可以将其合并到更新中:
update tickets t
set assigned_to = tt.assigned_to,
status = tt.status,
date = tt.date
from (select ticket_id,
max(new_value) filter (where field_name = 'Assigned_to') as assigned_to,
max(new_value) filter (where field_name = 'Status') as status,
max(date) as date
from (select distinct on (ticket_id, field_name) dtl.*
from daily_transaction_logs dtl
order by ticket_id, date desc
) tf
group by ticket_id
) tt
where tt.ticket_id = t.ticket_id;
推荐阅读
- android - FAILURE:构建失败并出现异常:React Native
- javascript - 在 Angular 项目中集成 JavaScript 日历
- jquery - 如何使用 jQuery 将属性值复制到兄弟属性
- bash - 在 PhpStorm 中使用一个快捷方式上传多个文件
- regex - 如何使用基于自定义关键字和正则表达式的应用程序脚本在谷歌驱动器中搜索文件?
- php - 包含反引号的字符串的意外 strcmp 行为
- scala - 使用案例类的折叠和空实例从 List[IO(Abstraction[List[A]]]] 移动到 IO[Abstraction[List[A]]]
- javascript - 单击按钮后如何将光标更改为自定义
- reactjs - 使用 django rest 序列化程序将数据添加到多个表
- ruby-on-rails - 无法在 Rails 6 上拯救 ActionDispatch::Http::Parameters::ParseError