質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.48%

  • SQL

    3075questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

  • BigQuery

    107questions

    BigQueryは、Google Cloud Platformが提供しているビッグデータ解析サービス。数TB(テラバイト)またはPB(ペタバイト)の膨大なデータに対し、SQL風のクエリを実行し、高速で集計・分析を行うサービスです。

SQL (BigQuery) で2つのテーブルを合わせたテーブルを作りたい

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 1,461

mashiroyuya

score 16

SQLをつかって以下のようなテーブルの作成(結合)がしたいです。
BigQueryでWITH句を使って一時テーブルを作成したいです。

現在以下の2つのテーブルがあります。

1 : コンテンツIDの一覧テーブル content_table

コンテンツID
1
2
:

2 : ユーザーのコンテンツの評価 (ユーザーが評価していないコンテンツについてのレコードはないです) rating_table

ユーザー コンテンツID 評価
A 1 1
A 3 4
B 1 5
C 5 1

この2つを使って次のような「コンテンツ(縦軸) vs ユーザー(横軸) の 評価テーブル(テーブルの各要素が評価値)」を作りたいです。

コンテンツID\ユーザー A B C ...
1 1 5 0
2 0 0 0
3 4 0 0
:

これのテーブルをSQLで作りたいのですが可能でしょうか?

現在 with句を使って 上記2つのテーブルを作り、最終的に「評価テーブル」を出すsqlコードが知りたいです。

with content_table as (
~
),
rating_table as (
~
)
これ以降に書く「評価テーブル」を吐き出すsql が知りたいです。
  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • unz.hori

    2018/03/15 15:13

    私はちょっとBigQueryには詳しくないので...。質問に指摘をするとしたらタグだけでなく、質問文にBigQueryでWITH句を使って一時表を作成したいという事を明記した方がいいと思います。

    キャンセル

  • mashiroyuya

    2018/03/15 15:14

    ありがとうございます。明記しました。

    キャンセル

  • キャンセル

回答 2

+1

出したい結果はクロス集計表ですね。
それを行う際の一時テーブル作成のSQLにwithを結びつける意図が分かりません。

クロス集計表ならこちら([SQL]クロス集計と、テンポラリテーブルを使用したクエリの分割)などが参考になるかと思います。
※リンク先はBiqQueryのものではありません。

また、array_agg()を使用して横軸を配列で纏めるのもありかなと思います。
取得結果の配列の操作はこちら(配列の操作

尚、当方BigQueryは使用したことはありません。
こんな記事(BigQueryで150万円溶かした人の顔)見つけたので、SQLの提示がおいそれとはできません。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/03/15 16:25

    確かに料金は怖いですね。SQL提示して責任持てませんね。

    キャンセル

  • 2018/03/15 16:29

    リンク先には「LIMITやWHERE句には何を書いてもテーブルをフルスキャンしてしまう」って書いてありますからねぇ。

    キャンセル

checkベストアンサー

0

postgresqlでwithが使えたので無理やりベタな方法で組んでみました。(COALESCEはOracleで言うNVL)

[重要] あくまでpostgresqlで試したコードです。応用してBigQueryで実行した結果については責任は取れませんのであしからず。

with t as (
  select
    id,
    COALESCE((select sum(eval) as a from rating_table as x where x.content_id = id and user_id = 'A'), 0) as a,
    COALESCE((select sum(eval) as a from rating_table as x where x.content_id = id and user_id = 'B'), 0) as b,
    COALESCE((select sum(eval) as a from rating_table as x where x.content_id = id and user_id = 'C'), 0) as c
  from
    content_table left join rating_table on content_table.id = rating_table.content_id
  group by id order by id
)
select * from t;

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/03/15 16:34

    なにぶん、SQLの偉い人ではないんでベタでかつpostgresql用です。

    キャンセル

同じタグがついた質問を見る

  • SQL

    3075questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

  • BigQuery

    107questions

    BigQueryは、Google Cloud Platformが提供しているビッグデータ解析サービス。数TB(テラバイト)またはPB(ペタバイト)の膨大なデータに対し、SQL風のクエリを実行し、高速で集計・分析を行うサービスです。