PostgreSQLで縦のデータを横のhstore型に変換する

PostgreSQLで縦持ちのデータを横持ちのhstore型へ変換する

サブテーブルのサブテーブルと連結した時に
複数レコードが取れるが1レコードとしてまとめたい場合がたまにあるので。

hstore有効化

さらっと有効化

CREATE EXTENSION hstore;

SQL

SELECT
  sub.ID ,
  hstore(
    array_agg(sub.SubID),
    array_agg(sub.DataID)::text[] -- 数値の場合はtext[]化
  ) AS SubID_DataID
FROM
( -- 適当なテーブルをでっち上げ
  SELECT '1' AS ID, '1' AS SubID, 99 AS DataID
  UNION ALL
  SELECT '1' AS ID, '2' AS SubID, 98 AS DataID
  UNION ALL
  SELECT '1' AS ID, '3' AS SubID, 97 AS DataID
  UNION ALL
  SELECT '2' AS ID, '4' AS SubID, 96 AS DataID
  UNION ALL
  SELECT '2' AS ID, '5' AS SubID, 95 AS DataID
  UNION ALL
  SELECT '2' AS ID, '6' AS SubID, 94 AS DataID
) sub
GROUP BY
  sub.ID;

psql結果

 id |          subid_dataid
----+---------------------------------
 2  | "4"=>"96", "5"=>"95", "6"=>"94"
 1  | "1"=>"99", "2"=>"98", "3"=>"97"
(2 行)

hstoreは< String, String >型なのでtext型へ変換する必要がある位

おまけ

縦のhstoreを1行のhstoreに結合する

一度キーとバリューを切り離して
再結合するひつようがあるかも
hstore[]型を受け取る関数があればいいんだけどな…

SELECT
  sub3.FLG,
  hstore(
    array_agg(sub3.SubID),
    array_agg(sub3.DataID)
  ) AS JOIN_DataID
FROM(
  SELECT
    'Dummy' AS FLG, -- グループ化用ダミー
    skeys(sub2.SubID_DataID) AS SubID,
    svals(sub2.SubID_DataID) AS DataID
  FROM(
    --ここから
    SELECT
      sub.ID ,
      hstore(
        array_agg(sub.SubID),
        array_agg(sub.DataID)::text[] -- 数値の場合はtext[]化
      ) AS SubID_DataID
    FROM
    ( -- 適当なテーブルをでっち上げ
      SELECT '1' AS ID, '1' AS SubID, 99 AS DataID
      UNION ALL
      SELECT '1' AS ID, '2' AS SubID, 98 AS DataID
      UNION ALL
      SELECT '1' AS ID, '3' AS SubID, 97 AS DataID
      UNION ALL
      SELECT '2' AS ID, '4' AS SubID, 96 AS DataID
      UNION ALL
      SELECT '2' AS ID, '5' AS SubID, 95 AS DataID
      UNION ALL
      SELECT '2' AS ID, '6' AS SubID, 94 AS DataID
    ) sub
    GROUP BY
      sub.ID
    -- ここまでの間は同じ
    )sub2
  )sub3
GROUP BY
  sub3.FLG;

psql結果

  flg  |                           join_dataid
-------+------------------------------------------------------------------
 Dummy | "1"=>"99", "2"=>"98", "3"=>"97", "4"=>"96", "5"=>"95", "6"=>"94"
(1 行)