我一直在尝试检查一个用户id(之前(在数字4,5或6处更改并结束(之后(的总次数。因此，在这种情况下，我们将为ID 62360的4比4计数器添加+1，为ID 61874的4比5计数器添加+1。
数据表：
ID Before After Date
61874 4 6 2021-06-04 14:53:25
61874 6 5 2021-06-04 14:55:30
62360 4 6 2021-06-03 14:18:31
62360 6 4 2021-06-03 14:18:33
62360 4 6 2021-06-03 14:18:37
62360 6 4 2021-06-03 14:18:39
期望输出：
Changes Tot.Count
4 to 4 1
4 to 5 1
4 to 6 0
5 to 4 0
5 to 5 0
5 to 6 0
6 to 4 0
6 to 5 0
6 to 6 0
我在获取上面的输出时遇到了问题，该输出将更改记录在更改/计数表中。
我只对每个ID的第一个Before案例和最后一个Before例感兴趣，但在起草时遇到了困难。我认为它必须是分组、CTE和滞后函数的组合。
select CASE
WHEN () THEN '4 to 4'
WHEN () THEN '4 to 5'
WHEN () THEN '4 to 6'
WHEN () THEN '5 to 4'
WHEN () THEN '5 to 5'
WHEN () THEN '5 to 6'
WHEN () THEN '6 to 4'
WHEN () THEN '6 to 5'
WHEN () THEN '6 to 6'
end as changes,
count(*) as Count_Changes
from data group by 2;
如果可能的话，python脚本可能能够做到这一点，但不确定如何将case语句实现为python函数
如果我理解正确，您需要每个ID的before列的第一个值和after列的最后一个值。这里first_value和last_value将帮助您。
首先，我们将使用以下查询获得每个组的值：
select
distinct on (id)
id,
first_value(before) over (partition by id order by date_) "fv",
last_value(after) over (partition by id order by date_ RANGE BETWEEN
UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING) "lv"
from data ) tab
以上查询将为您提供如下输出：
61874 4 5
62360 4 4
要获得确切的输出，请使用以下查询：
with cte as (
select *,
case when fv=4 and lv=4 then '4 to 4'
when fv=4 and lv=5 then '4 to 5'
when fv=4 and lv=6 then '4 to 6'
when fv=5 and lv=4 then '5 to 4'
when fv=5 and lv=5 then '5 to 5'
when fv=5 and lv=6 then '5 to 6'
when fv=6 and lv=4 then '6 to 4'
when fv=6 and lv=5 then '6 to 5'
when fv=6 and lv=6 then '6 to 6'
end "range" from (
select
distinct on (id)
id,
first_value(before) over (partition by id order by date_) "fv",
last_value(after) over (partition by id order by date_ RANGE BETWEEN
UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING) "lv"
from data ) tab
)
select tab.col1 "Changes", count("range") "Tot.Count" from (
values('4 to 4'),('4 to 5'),('4 to 6'),('5 to 4'),('5 to 5'),('5 to 6'),('6 to 4'),('6 to 5'),('6 to 6')
) tab(col1)
left join cte on tab.col1=cte."range"
group by 1 order by 1
演示
我想我会以不同的方式处理这个问题。实际上没有必要获得第一个"；在"；最后一个"；在"；值。也不需要列出before和after值的所有可能组合，因为它们可以生成。
您可以在最终查询中生成值的组合。你可以得到第一个"；在"；最后一个"；在"；在单独的查询中，然后将它们加入：
with ba (val) as (
values (4), (5), (6)
)
select ba_before.val as from_val, ba_after.val as after_val, count(a.id),
ba_before.val || ' to ' || ba_after.val as string_version
from ba ba_before cross join
ba ba_after left join
(select distinct on (id) id, before
from data
order by id, date asc
) b
on b.before = ba_before.val left join
(select distinct on (id) id, after
from data
order by id, date desc
) a
on a.id = b.id and
a.after = ba_after.val
group by 1, 2;
这里有一个db<gt；不停摆弄
如果您愿意，您可以直接从数据中生成ba列表：
with ba as (
select distinct val
from data cross join lateral
(values (before), (after)) v(val)
)
注意，这也可以将(id, date asc)/(id, date desc)上的索引用于子查询。如果您正在处理大量数据，这将大大提高性能。