array_aggを利用したメッセージ文を作成

2021年10月28日

前提

取得対象のテーブルには俳句の上五（上の句）、中七（中の句）、下五（下の句）をランダムに格納させています。指定の上五、中七、下七を連結させて俳句にさせます。

サンプルSQL

俳句の上五（上の句）のテーブル

with kamigo as (
 SELECT *
    FROM (
      VALUES
        (1, '古池や'),  
        (2, '柿食えば'), 
        (3, '秋深き')  
         ) AS t(bango, ku)

俳句の中七（中の句）のテーブル

), nakashichi as (
  SELECT *
    FROM (
      VALUES
        (4, '鐘が鳴るなり'), 
        (5, '蛙飛び込む'),  
        (6, '隣は何を')  
         ) AS t(bango, ku)

俳句の下五（下の句）のテーブル

), shimogo as (
  SELECT *
    FROM (
      VALUES
        (7, '法隆寺'), 
        (8, 'する人ぞ'),
        (9, '水の音')
         ) AS t(bango, ku)

指定の上五、中七、下五を連結させます

), union_tbl as (
  select
    ku
    ,1 as no
  from kamigo
  where bango = 1
  union all
  select
    ku
    ,2 as no
  from nakashichi
  where bango = 5
  union all
  select
    ku
    ,3 as no
  from shimogo
  where bango = 9

--  上記で連結させると上五、中七、下五の句がランダムで出力されます
--  
--  ku                |    no
--  --------------------------------
--  蛙飛び込む  |   2
--  古池や         |   1
--  水の音         |   3
--  
--  そのため「no」でソートさせます
), union_tbl_order as(
  select
    *
  from union_tbl
  order by no
)

--  「array_agg」で結合した配列を結合させます。そして「array_join」を用いて文字列にさせます
--  
--  message
--  --------------------------------
--  古池や 蛙飛び込む 水の音
select
  array_join(array_agg(ku), ' ') as message
from union_tbl_order