mysql - 查找自上次导出后为每个设备添加的应用列表

Question

我的问题涉及从以前的观察中不存在的系列中选择项目。我正在尝试采用 SQL: selecting rows where column value changed from previous row 中的技术.

我有一个应用程序，其中包含一个表，该表存储设备上安装的应用程序列表。设备每天提交此数据，并且每天保存完整列表。我每天都在导出新记录以导入报告系统，但数据变得非常大，因为我的报告数据集必须保留比实际应用程序大得多的时间范围。而且应用程序存储这些数据的方式对于报告来说并不是最佳的。我只想导出表明自上次执行数据导出以来设备上安装了新应用程序的记录。上次导出的 report_id 和来自设备的最新报告是已知的。数据结构不理想。本质上，我正在尝试将时间序列数据转换为更改日志。

我不拥有源应用程序，所以我无法更改数据库的数据结构、触发器等。

*** 有关数据结构和示例数据，请参阅 fiddle :http://sqlfiddle.com/#!9/ec6040/17

SQL:

-- Sample data: 
CREATE TABLE last_exported_reports
  (report_id int, mobile_device_id int);
INSERT INTO last_exported_reports 
  (mobile_device_id, report_id)
VALUES 
  (1, 1), (2, 6), (3, 7);

CREATE TABLE mobile_devices_denormalized
  (mobile_device_id int, last_report_id int);
INSERT INTO mobile_devices_denormalized 
  (mobile_device_id, last_report_id)
VALUES 
  (1, 4), (2, 6), (3, 8);

CREATE TABLE reports
  (`report_id` int, `mobile_device_id` int, `date_entered_epoch` bigint(32));
INSERT INTO reports
  (`report_id`, `mobile_device_id`, `date_entered_epoch`)
VALUES
  (1, 1, 1529981397691),
  (2, 2, 1529981397692),
  (3, 3, 1529981397693),
  (4, 1, 1529981397694),
  (5, 2, 1529981397695),
  (6, 2, 1529981397696),
  (7, 3, 1529981397697),
  (8, 3, 1529981397698);

CREATE TABLE mobile_device_installed_applications
    (`report_id` int, `identifier` varchar(8), `application_short_version` varchar(5));
INSERT INTO mobile_device_installed_applications
    (`report_id`, `identifier`, `application_short_version`)
VALUES
    (1, 'Chrome', 'c1.1'),   -- device 1
    (1, 'Word',   'w2.1'),   -- device 1
    (2, 'Skype',  's1.0'),   -- device 2
    (3, 'Excel',  'e3.0'),   -- device 3    
    (4, 'Chrome', 'c2.1u'),  -- device 1
    (4, 'Word',   'w2.1n'),  -- device 1
    (4, 'Excel', 'w2.0'),    -- device 1
    (5, 'Skype',  's1.0'),   -- device 2
    (6, 'Skype',  's1.9'),   -- device 2
    (7, 'Excel',  'e3.0'),   -- device 3
    (8, 'Excel',  'e3.0');   -- device 3


SELECT 
  mdd.mobile_device_id AS md_id, mdia.report_id, mdia.identifier, mdia.application_short_version AS ver
FROM 
  -- List of all devices
  mobile_devices_denormalized mdd
  INNER JOIN
    -- Add in apps
    mobile_device_installed_applications mdia
  ON
    -- Only if they are from the last report from a device
    mdia.report_id = mdd.last_report_id
  AND
    -- And only if the latest report has not already been exported
    mdia.report_id NOT IN (select report_id FROM last_exported_reports)
  AND
    -- And only if the app in the new report was not in the last exported report
    NOT EXISTS (
      SELECT * 
      FROM mobile_device_installed_applications exported
      WHERE exported.identifier = mdia.identifier
      AND  exported.report_id = mdd.last_report_id
    )
;

如果我在没有 NOT EXISTS 子句的情况下运行上面的代码，它会正常工作，因为我会按预期获得新报告的所有应用程序记录。当我在最后一步添加去除重复应用程序的子查询时，我没有得到任何结果。

如果在没有子查询子句的情况下运行结果:

md_id    report_id    identifier    ver
1        4            Chrome        c2.1u
1        4            Word          w2.1n
1        4            Excel         w2.0
3        8            Excel         e3.0

• 这对于设备 1 是正确的，因为报告 4 最最近且不是以前导出的报告 (4 != 1)
• 这对设备 2 是正确的，因为报告 6 最最近，但它已经被导出，因此没有记录被导出。
• 这对于设备 3 是正确的，因为报告 8 最最近且不是以前导出的报告 (8 != 7)

添加最终子查询子句后的结果:

没有记录。

预期结果:

md_id    report_id    identifier  ver
1        4            Excel       w2.0

上面的记录应该已经打印出来了，因为它在设备 #1 的最新报告 (report_id=4)，并且上次导出时该应用不在设备上 (report_id=1)。

Answer 1

SELECT a.* 
FROM (
    SELECT  
        mdd.mobile_device_id, 
        mdia.report_id, 
        mdia.identifier, 
        mdia.application_short_version AS ver
    FROM mobile_devices_denormalized mdd
    INNER JOIN mobile_device_installed_applications mdia ON mdia.report_id = mdd.last_report_id
    ) AS a
LEFT JOIN (
    SELECT  
        ler.mobile_device_id, 
        mdia.report_id, 
        mdia.identifier, 
        mdia.application_short_version AS ver
    FROM last_exported_reports ler
    INNER JOIN mobile_device_installed_applications mdia ON mdia.report_id = ler.report_id
    ) AS b ON a.mobile_device_id = b.mobile_device_id  AND a.identifier = b.identifier 
WHERE b.report_id IS NULL;

逻辑:将新报告的应用列表与上次为同一设备导出的报告中的应用列表进行匹配，并从新报告中选择不匹配的应用列表。