主页 / 问题 / 1603475
Accepted
Дарья Смагина
Asked:2025-01-27 15:39:57 +0000 UTC2025-01-27 15:39:57 +0000 UTC

优化SQL查询，分几段计算

  • 772

有一个 postgresql 和一个带有事实标识符Fact_id及其创建日期的表，Created_at类型为：

事实编号 创建时间
5770 2024-12-20 15:55:54
5775 2024-12-20 10:52:22
6122 2024-12-25 10:10:09
6235 2024-12-21 12:23:58
5775 2024-12-25 18:17:47
6122 2024-12-23 18:53:15
5770 2024-12-22 14:22:16
6122 2024-12-26 14:16:38

Fact_id我需要计算过去一天、前一天和前两天创建了多少。但在结果表中，仅反映Fact_id在过去 24 小时内创建的值。

我已经受够了嵌套请求：

select fact_id, int_1, int_2, int_3
from (select fact_id,
     COUNT(case when created_at >= (now() - interval '1 day') then 1 end) as int_1,
     COUNT(case when created_at between (now() - interval '1 day') and (now() - interval '2 day') then 1 end) as int_2,
     COUNT(case when created_at between (now() - interval '2 day') and (now() - interval '3 day') then 1 end) as int_3
     from table
     where created_at >= (now() - interval '3 day')
     group by fact_id) as d
where int_1 > 0

结果，我得到一张表格：

事实编号 int_1 int_2 int_3
6122 1 1 0

是否有可能以某种方式优化或缩短代码以获得相同的结果？ COUNT 条件中的句点几乎是手动设置的，特别令人困惑。

sql

1 个回答

  1. Best Answer

    参见 CROSSTAB 示例

    在样本数据中，我添加了行来考虑不同的情况，并将日期向前移动一天以获得所需的今天、昨天等。

    首先，我们计算dd每一行从今天（往前）算起的天数（）。我们将使用这个值来分组（计数）行。

    select *
  ,extract('day' from now()-created_at)dd
from logs
    事实ID 创建于 日
    5770 2024-12-21 15:55:54 6
    5775 2024-12-21 10:52:22 6
    6122 2024-12-26 10:10:09 1
    6235 2024-12-22 12:23:58 5
    5775 2024-12-26 18:17:47 0
    6122 2024-12-24 18:53:15 2
    5770 2024-12-23 14:22:16 4
    6122 2024-12-27 14:16:38 0
    9990 2024-12-25 14:16:38 2

    分组后，我们将获得每个 fact_id（dd，cnt）的数据（在本例中为 3 行）。
    之后，我们将根据 fact_id 将该表按一行进行 PIVOT。由于并非所有日期都可以填写，我们将使用 crosstab 的第二个参数，它指定源中应存在的所有值 (dd)。在这种情况下，值从0到2。

    CREATE EXTENSION IF NOT EXISTS tablefunc;
SELECT * FROM CROSSTAB($$
  select fact_id
    ,extract('day' from now()-created_at)dd 
    ,count(*) cnt
  from logs
  where created_at >= (now() - interval '3 day')
  group by fact_id,dd
  order by fact_id,dd
  $$,
   $$ select n from generate_series(0,2)n
  $$
)
AS ct (fact_id int, "int_1" bigint, "int_2" bigint, "int_3" bigint)
where int_1>0
    事实ID int_1 int_2 int_3
    5775 1 无效的 无效的
    6122 1 1 1

    为了好玩，如果没有过滤器，结果将是这样的

    事实ID int_1 int_2 int_3
    5775 1 无效的 无效的
    6122 1 1 1
    9990 无效的 无效的 1

    分组后的 CROSSTAB 源表

    事实ID 控制数 日
    5775 1 0
    6122 1 1
    9990 1 2
    6122 1 0
    6122 1 2

    这是一个使用常量（或参数）代替 now() 的示例。
    小提琴

    • 2

相关问题