sqlで集計をしたい

Question

前提・実現したいこと

sql(Redshift)の初心者です。集計について教えてください。

会員数と売上を店舗、EC、店舗＋ECの３つに分けて集計したいのですが
店舗＋ECの集計に苦戦しております。

■テーブル

kaiinno　salesstore　SKU　salesamt2 discount3
１　9999　みかん 100 50
２　1111　みかん 100 50
３　2222　りんご 300 30
４　3333　すいか 600 0
４　9999　すいか 600 10

※9999のみECでその他は店舗での購入としたい
※店舗コード　salesstore
■実現したいこと
チャネル　会員数　売上
EC　
店舗
EC＋店舗

ここに質問の内容を詳しく書いてください。
（例）PHP(CakePHP)で●●なシステムを作っています。
■■な機能を実装中に以下のエラーメッセージが発生しました。

該当のソースコード

SELECT COUNT(DISTINCT kaiinno) AS 会員数,
COUNT(sku)AS SKU,
SUM(salesamt2 + discount3) AS 売上,
CASE salesstore WHEN '9999' THEN 'EC' ELSE '店舗' END AS チャネル
FROM saleswithcustomers
GROUP BY (CASE salesstore WHEN '9999' THEN 'EC' ELSE '店舗' END)

試したこと

ROLLUPやCUBEを試しましたがエラーが出てしまいました。

補足情報（FW/ツールのバージョンなど）

ここにより詳細な情報を記載してください。
Redshift

Comments

[Orlofsky]

>会員ID　店舗コード　アイテム　売上
とSELECT文の列名が結びつかないので、上記の列をCREATE TABLE の列名と同じもので書いては？CREATE TABLE 文も載せては？
店舗コードに全角文字と半角文字が混在しているのはおかしくないですか？
[Amazon Redshift]タグを追加しては？存在しないタグは追加依頼します。https://teratail.com/help#add-tag

[kkkkkkkkkkkkl]

ありがとうござます。SELECT分と列名を間違えておりました。申し訳ありません。
修正いたしました。

[Orlofsky]

>CASE salesstore WHEN '9999' THEN 'EC' ELSE '店舗' END
とあるのにデータは９９９９ で表示されています。

９９９９ と 9999 は別の値だと理解できないとプログラマーをやるのは難しいです。テーブルの内容の表示も https://teratail.com/help/question-tips#questionTips3-7 の [コード] に修正しては？

それと、CASEを無用なかっこでくくるのは止めましょう。

[kkkkkkkkkkkkl]

コードに修正しました。使い方教えていただきありがとうございます。

[sazi]

どんなエラーですか？

[kkkkkkkkkkkkl]

返信ありがとうございます。以下エラーです。色々調べてみたのですが集計方法がわからず
なにを利用すればいいか教えていただけると助かります。
SQLエラー [500310] [42883]: [Amazon](500310) Invalid operation: function cube(text) does not exist;

[sazi]

cube(text) does not exist
となっていますが、質問にあるSQLで出たエラーでしょうか？
→失礼。質問のSQLではどんな問題があるのでしょうか？

[kkkkkkkkkkkkl]

質問に記載したSQLではEC＋店舗の合計を出すことができないのです。

Answer 1

残念ながら、ROLLUPもCUBEも2020-06-22の時点でRedshiftがサポートしていません。

WITHとUNIONを活用すれば大丈夫です。

WITH 
ec_or_store_agg AS
(
SELECT
  COUNT(DISTINCT kaiinno)                               AS 会員数,
  COUNT(sku)                                            AS SKU,
  SUM(salesamt2 + discount3)                            AS 売上,
  CASE salesstore WHEN '9999' THEN 'EC' ELSE '店舗' END AS チャネル
FROM saleswithcustomers
GROUP BY (CASE salesstore WHEN '9999' THEN 'EC' ELSE '店舗' END)
),

ec_and_store_agg AS
(
SELECT
  SUM(会員数) AS 会員数,
  SUM(SKU)   AS SKU,
  SUM(売上)  AS 売上,
  'EC＋店舗' AS チャネル
FROM ec_or_store_agg
GROUP BY (SELECT 1)  -- 全てを一つGROUPにする
)

SELECT * FROM ec_or_store_agg
UNION
SELECT * FROM ec_and_store_agg
;

Comments

[kkkkkkkkkkkkl]

ありがとうございます。試してみます。