在SQL（PostgreSQL）中选择动态行对

Question

我的目标是创建动态的线组（按实际类型和颜色分组）

我不知道是否可能仅使用一个查询来完成。

但我想根据number_per_group列创建线组（产品是类型和颜色），并且我想根据日期顺序进行分组（按DATE排序）。

一个NB_PER_GROUP数为2的单个产品将被排除在最终结果之外。

Table :

-----------------------------------------------
NUM |  TYPE |   COLOR  | NB_PER_GROUP   | DATE
-----------------------------------------------
0   |  1    |   1      |    2           |  ...
1   |  1    |   1      |    2           |
2   |  1    |   2      |    2           |
3   |  1    |   2      |    2           |
4   |  1    |   1      |    2           |
5   |  1    |   1      |    2           |
6   |  4    |   1      |    3           |
7   |  1    |   1      |    2           |
8   |  4    |   1      |    3           |
9   |  4    |   1      |    3           |
10  |  5    |   1      |    2           |


Results :

------------------------
GROUP_NUMBER   |  NUM  | 
------------------------
0              |   0   |
0              |   1   |
~~~~~~~~~~~~~~~~~~~~~~~~
1              |   2   |
1              |   3   |
~~~~~~~~~~~~~~~~~~~~~~~~
2              |   4   |
2              |   5   |
~~~~~~~~~~~~~~~~~~~~~~~~
3              |   6   |
3              |   8   |
3              |   9   |

如果您有另外一种解决这个问题的方法，我会接受它。

- Sarah Taylor

---

GROUP = 0 和 GROUP = 2 有什么区别？ - Hamidreza

Group 2 是产品 (1,1) 的第二组。同一产品有多个组：组 0：第一组 (1,1)，组 2：第二组 (1,1)。 - Sarah Taylor

这似乎是你应该在应用层中完成的任务，通过迭代带有 ORDER BY 的查询结果。SQL 查询的结果是一个“关系”，而你所需的输出与关系完全不同。 - Daniel Sparing

所以我们都认为，仅使用 SQL 查询（不包括 PL/SQL）无法得到这个结果。 - Sarah Taylor

Answer 1

这样的东西怎么样？

select max(gn.group_number) group_number, ip.num
from products ip
join (
    select date, type, color, row_number() over (order by date) - 1 group_number
    from (
        select op.num, op.type, op.color, op.nb_per_group, op.date, (row_number() over (partition by op.type, op.color order by op.date) - 1) % nb_per_group group_order
        from products op
    ) sq
    where sq.group_order = 0
) gn
 on ip.type = gn.type
and ip.color = gn.color
and ip.date >= gn.date
group by ip.num
order by group_number, ip.num

只有在每种类型和颜色的组合中，nb_per_group值相同时，此方法才有效。如果需要，可能还需要唯一的日期，但这可能可以解决。

最内层子查询按类型和颜色对行进行分区，按日期排序，然后计算行号模nb_per_group；这形成了一个基于0的组计数，每次超过nb_per_group时重置为0。

下一级子查询找到我们在较低子查询中映射的所有0值，并为它们分配组号。

最后，最外层的查询将产品表中的每一行与一个组号相关联，该组号计算为在此产品日期之前分裂的最高组号。

- Peter G