Postgres: View 上での連番追加方法

背景

以下で Unpivot 化した後で、Power Query 側で Index を追加したくなったので、View での実現方法を調査

概要

• ROW_NUMBER() 使えばいいだけでした。

詳細

元データ

単純な連番を付ける

SQL

単純な連番追加

select 
	ROW_NUMBER() OVER() AS "ID",
	*
from (
	SELECT 
		"ID" AS "OriginalID",	
		unnest(array['First', 'Second', 'Third']) AS "Order",
		unnest(array["Interests1", "Interests2", "Interests3"]) AS "Interest",
		unnest(array["Knowledges1", "Knowledges2", "Knowledges3"]) AS "Knowledge"
	FROM pivotTable
	ORDER BY "ID"
) AS Unpivot;

特定の列で並び替え手、連番を付ける

SQL

並び替えて連番追加

select 
	ROW_NUMBER() OVER(order by "Order" nulls last)  AS "ID",
	*
from (
	SELECT 
		"ID" AS "OriginalID",	
		unnest(array['First', 'Second', 'Third']) AS "Order",
		unnest(array["Interests1", "Interests2", "Interests3"]) AS "Interest",
		unnest(array["Knowledges1", "Knowledges2", "Knowledges3"]) AS "Knowledge"
	FROM pivotTable
	ORDER BY "ID"
) AS Unpivot
order by "Order";

参考