Bigqueryで含まれる言葉別かつ日付別に集計をしたい

Question

Bgquery上に以下のようなテーブルがあった場合に、日付ごとかつキーワード別に数を集計したいのですがそのようなことは可能でしょうか？

日付　│テキスト
------------│---------------
1/1. │楽しかったです
1/1. 　│そこそこ
1/1. │微妙
1/1. │微妙
1/2 │楽しかったです
1/2. │そこそこ
1/2　　│そこそこ

↓集計

日付│楽し│そこ│微妙
ｰｰｰｰｰｰｰｰｰｰｰｰｰｰｰｰｰｰｰｰｰｰｰｰｰ
1/1　│1　│1　　│２
1/2　│1　│2　　│０

Answer 1

こんな感じでしょうか。

ご希望通り列として持つなら https://dev.classmethod.jp/articles/sql-data-horizontal-vertical/ の「縦持ちを横持ちに」を参考に。
配列がいいなら array_agg で。

with data as (
  select date('2022-01-01') as mydate,'楽しかったです' as str union all
  select date('2022-01-01') as mydate,'楽しかったです' as str union all
  select date('2022-01-01') as mydate,'そこそこ' as str union all
  select date('2022-01-02') as mydate,'楽しかったです' as str
),
list as (
  select '楽し' as word union all
  select 'そこ' as word union all
  select '微妙' as word
),
result as (
  select * from data, list
  where data.str like '%'||list.word||'%'
)
select mydate, word, count(*)
from result
group by mydate, word

結果

2022-01-01 | 楽し | 2
2022-01-01 | そこ | 1
2022-01-02 | 楽し | 1

Comments

[Roy_tofu3]

ありがとうございました!!!
頂いたクエリでできそうです！

ちなみに検索キーワードに使うキーワードをor条件で複数入れる場合は

list as (
select '楽し' OR '楽ぴ' as word union all
select 'そこ' as word union all
select '微妙' as word
),
のようにするのかと思ったのですが、どのように書けば構文上正しいかご教示頂けないでしょうか。。
のように

[68user]

こんな感じでしょうか。

with data as (
select date('2022-01-01') as mydate,'楽しかったです' as str union all
select date('2022-01-01') as mydate,'楽しかったです' as str union all
select date('2022-01-01') as mydate,'楽ぴ' as str union all
select date('2022-01-01') as mydate,'そこそこ' as str union all
select date('2022-01-02') as mydate,'楽しかったです' as str
),
list as (
select '楽し' as word, '楽し' as match_word union all
select '楽し' as word, '楽ぴ' as match_word union all
select 'そこ' as word, 'そこ' as match_word union all
select '微妙' as word, '微妙' as match_word
),
result as (
select * from data, list
where data.str like '%' || list.match_word || '%'
)
select mydate, word, count(*)
from result
group by mydate, word