时间戳

首页 > 解决方案 > SQL-92 过滤结果并写入集合中的第一行

sql - SQL-92 过滤结果并写入集合中的第一行

问题描述

我很少使用 SQL 但代码进行过滤，现在我需要用 sql-92 标准填充表中的列。

有一张表包含用户 ID 和事务状态。因此，如果用户和事务第一次尝试成功、多次尝试或根本不成功，则输出表应包含带有标志的列。我不应该汇总用户的输出并将其保留为“基于事务”的问题！规则是：

  1. 如果用户只有状态 200 或 202，则在表中该用户的第一行和该用户的其他行为 Null
  2. 如果用户 satatuses 包含 200 或 202 以及任何其他状态，则用户在表中该用户的第一行“多次尝试成功”，而该用户的其他行为 Null
  3. 如果用户仅满足 200 或 202 之后的其他状态，则用户在表中该用户的第一行“不成功”，而该用户的其他行为 Null。

所以我应该改变这个：

id  dt          user_id status_
26  01.01.2020  111     200
12  02.01.2020  112     200
7   03.01.2020  112     202
19  04.01.2020  113     400
87  05.01.2020  113     200
9   06.01.2020  114     400
14  07.01.2020  115     400
1   08.01.2020  115     400
65  09.01.2020  116     202
34  10.01.2020  116     202

对此：

id  dt          user_id status_   custom_col    
26  01.01.2020  111     200       "Successful with first try"
12  02.01.2020  112     200       "Successful with first try"
7   03.01.2020  112     202       Null
19  04.01.2020  113     400       "Successful with multiple try"
87  05.01.2020  113     200       Null
9   06.01.2020  114     400       "Unsuccessful"
14  07.01.2020  115     400       "Unsuccessful"
1   08.01.2020  115     400       Null
65  09.01.2020  116     202       "Successful with first try"
34  10.01.2020  116     202       Null

所以我想做什么，我的算法：

  1. 获取每笔交易
  2. 检查此事务中的用户是否返回对应于规则 1 或规则 2 或规则 3 的子序列
  3. 仅在该用户的第一行中写入。

所以我只来了如何为用户填充每一行，而不仅仅是第一行：

SELECT id, user_id, status_, 
    CASE
        WHEN user_id IN (SELECT user_id FROM test t1
                         WHERE NOT EXISTS 
                         (SELECT user_id, status_ FROM test t2 WHERE t1.user_id = t2.user_id AND t2.status_ > 202))
        THEN 'Successful with first try'
        WHEN user_id IN (SELECT user_id FROM test t1
                         WHERE NOT EXISTS 
                         (SELECT user_id, status_ FROM test t2 WHERE t1.user_id = t2.user_id AND t2.status_ <= 202 ))
        THEN 'Unsuccessful'
        ELSE 'Successful with multiple try'
        END as new_col
FROM test

问题是我怎样才能把它只写到集合中的第一行？我明白，我应该以某种方式获取子集的 TOP 1 id 并检查带有 user_id 的事务的 id 是否是正确的 id，但是我的尝试以非常深的嵌套情况结束，所以我放弃并写在这里寻求帮助:)

如果您需要，这是架构：

create table test (
    id serial primary key,
    user_id int,
    status_ int,
    some_col text
);

insert into test values (26, 111, 200, 'abs');
insert into test values (12, 112, 200, 'abs');
insert into test values (7, 112, 202, 'abs');
insert into test values (19, 113, 400, 'abs');
insert into test values (87, 113, 200, 'abs');
insert into test values (9, 114, 400, 'abs');
insert into test values (14, 115, 400, 'abs');
insert into test values (1, 115, 400, 'abs');
insert into test values (65, 116, 202, 'abs');
insert into test values (34, 116, 202, 'abs');

非常感谢！

标签： sqlpostgresql

解决方案

您可以使用窗口函数：

select t.*,
       (case when row_number() over (partition by user_id order by dt) > 1
             then null
             when count(*) filter (where t.status_ not in (200, 202)) over (partition by user_id) = 0
             then 'Successful with first try'
             when count(*) filter (where t.status_ in (200, 202)) over (partition by user_id) > 0
             then 'Successful with multiple tries'
             else 'Unsuccessful'
        end) as custom_col             
from test t;

这是一个 db<>fiddle。

编辑：

您可以在没有窗口函数的情况下将其表示为：

select t.*,
       (case when id > min_id then null
             when num_not200s = 0 then 'Successful with first try'
             when num_200s > 0 then 'Successful with multiple tries'
             else 'Unsuccessful'
        end) as custom_col
from test t join
     (select user_id, min(id) as min_id,
             sum( case when t.status_ not in (200, 202) then 1 else 0 end) as num_not200s,
             sum( case when t.status_ in (200, 202) then 1 else 0 end) as num_200s
      from test t
      group by user_id
     ) u
     using (user_id)
order by id;

这也不使用 Postgres 特定的扩展，例如::或更现代的语法，例如filter.

推荐阅读