HOW TO TD(User Engagement)Treasure Data User Engagement

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

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

データマネジメントチームの竹ヶ原 旭です。
Treasure Data CDPではSQLクエリエンジンとしてPrestoが搭載されております。あまり使用頻度は高くないかもしれませんがSQL結果を文字列(文字メッセーシ)としてログ出力する場合があるかもしれません。その時、「array_arg」を使用した文字列作成方法をご紹介いたします。

前提

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

サンプル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

 

さいごに

Prestoのクエリエンジンは集計などに利用される強力な関数が多数用意されています。上記のサンプルのような内容はあまり実際の業務では利用されることは少ないかもしれませんが手続き言語のような柔軟処理が可能であることをご理解いただけたかと思われます。

竹ヶ原 旭

Data Managementチーム

エンタープライズ向けのシステムエンジニアとしてキャリアをスタート。航空会社や電力などのユーティリティー企業のシステム構築に携わる。その後、デジタル系広告会社にてテクニカルディレクター、外資ITベンダーでプリセールスを経験。映画配給会社、テレビ局、ラジオ局、出版社などのメディア、飲料やConsumer Packaged Goods(CPG)メーカーなどのクライアントに対して、テクニカル観点でのクリエイティブ制作・デジタルマーケティングおよび既存のアナログ的な業務へのデジタル化支援に従事。また、UXやDesign Thinkingを活用した新規事業開発支援なども担当。2019年よりトレジャーデータに参画。データマネジメントチームの一員として技術的側面からTreasure Data CDPの導入・運用支援を行う。

得意領域 : データアーキテクト、Workflow、データベース、Javascript、Swift、ETL、Custom Script、MA

Back to top button