时间戳

首页 > 解决方案 > 选择与日期之间的值的新行相关的最新行

sql - 选择与日期之间的值的新行相关的最新行

问题描述

我有一个包含四个字段的表：parent_id、field_name、field_value 和inserted_at。

当“日期”字段名称的一行的值介于日期 1 和日期 2 之间时，我需要在父 ID 下找到所有最新的行。

当前 sql 不返回日期之间的行，它也返回一些较旧的行。我认为如果我可以在 where 子句中放置一个窗口表达式，那么它会起作用，但是现在对于子查询和 CTE，某些东西不会在连接中保留并让坏数据重新进入。

SELECT all_fields.parent_id, all_fields.field_name, all_fields.field_value FROM (
      WITH sorted_table AS (
        SELECT newest_rows.parent_id from (
          SELECT
            row_number() over (partition by o3.parent_id, o3.field_name order by o3.inserted_at desc) as row_n,
            parent_id
          FROM table o3
          where field_name = 'date'
          and field_value between '2019-01-01' and '2019-01-30'
        )
        as newest_rows
        WHERE newest_rows.row_n = 1
      )
      SELECT row_number() over (partition by o3.parent_id, o3.field_name order by o3.inserted_at desc) as row_n, o3.parent_id, o3.field_name, o3,field_value
      FROM table o3
      left join sorted_table so
      on so.parent_id = o3.parent_id
    ) as all_fields
    WHERE all_fields.row_n = 1;

给定数据库行：

parent_id   field_name  field_value inserted_at
1           date        jan 25      july 1
1           date        feb 2       july 2
1           name        frank       july 1
1           name        nancy       july 3
2           date        dec 3       july 1
2           date        jan 5       july 2
2           name        allison     july 1
2           name        debbie      july 2
3           date        dec 1       july 1
3           date        jan 6       july 2
3           name        roger       july 1
3           name        claire      july 3

日期范围：1 月 1 日至 30 日

结果应该是：

parent_id   field_name  field_value inserted_at
2           date        jan 5       july 2
2           name        debbie      july 2
3           date        jan 6       july 2
3           name        claire      july 3

date 字段是最新的并且在范围内，因此它返回 parent_id 下每个 field_name 的所有最新行。

标签： sqlpostgresql

解决方案

假设您的字段值采用合理的格式（即 YYYY-MM-DD）。然后具有最大日期的 parent_id 由以下方式提供：

select o3.*
from o3
where field_name = 'date' and
      field_value >= '2019-01-01' and
      field_value < '2019-02-01'
order by field_value desc
limit 1;

然后我想你想要：

select o3.*
from o3
where (parent_id, inserted_at) = (
          (select o3.parent_id, o3.inserted_at
           from o3
           where o3.field_name = 'date' and
                 o3.field_value >= '2019-01-01' and
                 o3.field_value < '2019-02-01'
           order by o3.field_value desc
           limit 1
          );

推荐阅读