如何选择所有具有相同时间顺序的值？

Question

我将尽力进行翻译，请查阅以下内容：

我将玩家的所有移动与相关Game-Id保存在一张表中。

移动的执行顺序也被保存。

表格：

id | move     | order
---+----------+------
 1 | Attack   | 1
 1 | Defense  | 2
 1 | Defense  | 3
 1 | Fly      | 4
 2 | Attack   | 1

例如：在游戏ID 1中，他首先进行攻击、防御、防御，然后飞行。

我现在想选择所有具有与我指定的时间顺序相同的游戏ID。

伪代码

SELECT DISTINCT id FROM Table
WHERE Player 
first Attack
second Defense
third Defense 

伪代码举例，即使未指定第四个动作“飞”，也将返回ID 1。

- Ojav

---

我觉得你的问题有点不清楚。你所说的“相同的时间顺序”是什么意思？是指从相同的三步开始吗？还是在任何位置但相邻的相同三个步骤？或者是按照那个顺序的三个步骤，但也可以有中间步骤呢？ - Gordon Linoff

是的，从相同的三个步骤开始。感谢您的回答<3 - Ojav

Answer 1

您可以使用row_number()和聚合函数来实现：

select id
from (select t.*, row_number() over(partition by id order by "order") rn from mytable t) t
group by id
having
    max(case when rn = 1 and move = 'Attack' then 1 end) = 1
    and max(case when rn = 2 and move = 'Defense' then 1 end) = 1
    and max(case when rn = 3 and move = 'Defense' then 1 end) = 1

顺便提一下：order 不是一个明智的列名，因为它与所有 RDBMS 中的保留字冲突。

- GMB

Answer 2

如果您有逗号分隔的动作列表，如：'Attack,Defense,Defense,Fly'，并且希望获得相同顺序的所有游戏ID，则可以使用string_agg()来实现：

select "id"
from tablename
group by "id"
having string_agg("move", ',' order by "order") = 'Attack,Defense,Defense,Fly'

请查看演示。
以下是您样本数据的结果：

| id  |
| --- |
| 1   |

- forpas

Answer 3

假设您的意思是希望拥有相同的初始移动集，那么我认为以下代码可以满足您的需求：

SELECT id
FROM (SELECT id, ARRAY_AGG(move ORDER BY "order") as moves
      FROM t
      WHERE order <= 3
     ) im
WHERE moves = ARRAY['Attack', 'Defense', 'Defense'];

这只是检查前三个移动步骤。你也可以表述为：

SELECT id
FROM (SELECT id, ARRAY_AGG(move ORDER BY "order") as moves
      FROM t
      WHERE order <= CARDINALITY(ARRAY['Attack', 'Defense', 'Defense'])
     ) im
WHERE moves = ARRAY['Attack', 'Defense', 'Defense'];

- Gordon Linoff

Answer 4

您可以尝试在 CTE 中使用有序的 array_agg：

WITH j AS (
  SELECT id, array_agg(move ORDER BY "order") AS moves FROM t GROUP BY id 
) SELECT id FROM j WHERE moves = '{Attack,Defense,Defense,Fly}';

 id 
----
  1
(1 Zeile)

- Jim Jones

Answer 5

对于一个通用的解决方案，您可以尝试类似以下的方法

Select
    Id,
    max(first)  m1,
    max(second) m2,
    max(third)  m3,
    max(fourth) m4
from 
(Select
     Id, 
     Case when order = 1 then move else "" as first
     Case when order = 2 then move else "" as second
     Case when order = 3 then move else "" as third
     Case when order = 4 then move else "" as fourth
 From table) as flat_table
Group by id 

这将为您提供一个表格，您可以在其中应用移动编号的条件或按移动模式进行分组等。它相当低效，但非常灵活。

如果您需要更高效的解决方案，则可以在从内部表格本身选择时应用 where 子句。

- Aditya