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

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

ただいまの
回答率

90.48%

  • MySQL

    6005questions

    MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

  • SQL

    2472questions

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

SQL timestamp型の日付でjoin

解決済

回答 1

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 955

sato_day

score 14

度々申し訳ありません、、

先ほどtimestamp型の日付から年月日を抜き出す方法を教えて頂き

SELECT 
cast(time as date), 
COUNT(distinct id) 
FROM a 
WHERE time > '2016-05-01' 
Group by cast(time as date)

で年月日ごとのid数を出せたのですが
これに、bという売上テーブルとjoinさせたいのですが
うまくいきません。。

aは登録者のデータで
bは売上データです。

bは

SELECT 
cast(time as date), 
COUNT(distinct id) ,
Sum(price) as price
FROM b
WHERE time > '2016-05-01' 
Group by cast(time as date)

ですると、日ごとの売上数字が合います。

これを登録者のデータとjoinさせ

SELECT 
cast(t1.time as date), 
COUNT(distinct t1.id)as UU ,
Sum(t1.price) as price,
COUNT(distinct t2.id)as 新規UU
FROM b t1 left join a t2 on t1.id= t2.id
and cast(t1.time as date)=cast(t2.time as date)
WHERE t1.time > '2016-05-01' 
Group by cast(t1.time as date)

とすると、新規UU数に差異が出てしまいます。
(新規UUが少ない)

これはどのようにSQLを変更したらよろしいでしょうか、、

また
WHERE t1.time > '2016-05-01' 
の箇所を、今日を含めない過去30日とするために
WHERE t1.time BETWEEN(curdate() - INTERVAL 30 DAY) AND (curdate() +INTERVAL 1 DAY)
とすると
syntax error at or near ”30”
というエラーが出てしまいます。

これの解消方法もご教示ください。。

【追記】
■aテーブルは、1行にログインしたidのデータが時間ごと入っている
→日単位でのログインid数を知りたい。

■bテーブルも1行に時間ごとの売上データがid別に入っている
→課金した人のid数と金額が知りたい

出したいのは日ごとの以下となります。

time  price  UU(課金した人数)  新規UU(ログインした人数)
2016-05-01 500  7  80
2016-05-02 700  2  40

  • 気になる質問をクリップする

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

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

  • KiyoshiMotoki

    2016/05/11 18:30

    ご提示のSQL文、タイポや明らかな構文エラーが所々にありますので、まずはそれを修正願います。また、前回のご質問で指摘があったように、可能な範囲で構わないのでテーブル定義を提示してください。その方が、より具体的な回答を得やすくなると思います。

    キャンセル

  • sato_day

    2016/05/11 19:17

    すみません、タイポとは何でしょう、、 構文エラーとのことですが、一応実行処理は完了するのですがおかしい構文でしょうか?

    キャンセル

  • KiyoshiMotoki

    2016/05/11 19:27

    > タイポとは何でしょう 失礼しました、誤記のことです。 http://e-words.jp/w/%E3%82%BF%E3%82%A4%E3%83%9D.html > おかしい構文でしょうか? 私が知る限り、"t1.distinct id"という構文は存在しません。

    キャンセル

  • sato_day

    2016/05/11 19:48

    おっしゃる通りです、、すみません。 追記として記載してみたのですが分かりづらかったらすみません、、

    キャンセル

回答 1

checkベストアンサー

0

情報の追記・訂正、ありがとうございます。

しかし、sato_day様が意図するデータを1クエリ(1回のSQL実行)で取得することは、恐らく不可能です。
(方法はあるかもしれませんが、少なくとも私には思いつきません)
その理由は、この回答の下の方を参照してください。

というわけで、以下のように2回に分けて実行し、(JavaやPHPなどの)プログラム側で望む形にデータを整形してやる必要があるはずです。

SELECT 
  CAST(time AS date) AS day, 
  SUM(price) AS price, 
  COUNT(DISTINCT id) AS UU 
FROM b 
WHERE time >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) 
  AND time < CURRENT_DATE() 
GROUP BY day 

SELECT 
  CAST(time AS date) AS day, 
  COUNT(DISTINCT id) AS 新規UU 
FROM a 
WHERE time >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) 
  AND time < CURRENT_DATE() 
GROUP BY day 

 意図するデータを1クエリで取得できない理由

以下のように、上の2つのクエリをJOINしてやると、1クエリで取得できるように思えるかもしれません。

※注意 : 動作確認していません

SELECT  
  t1.day, 
  t1.UU, 
  t1.price, 
  t2.新規UU 
FROM (
  SELECT 
    CAST(time AS date) AS day, 
    SUM(price) AS price, 
    COUNT(DISTINCT id) AS UU 
  FROM b 
  WHERE time >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) 
    AND time < CURRENT_DATE() 
  GROUP BY day 
) AS t1 
LEFT OUTER JOIN (
  SELECT 
    CAST(time AS date) AS day, 
    COUNT(DISTINCT id) AS 新規UU 
  FROM a 
  WHERE time >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) 
    AND time < CURRENT_DATE() 
  GROUP BY day 
) AS t2 
  ON t1.day = t2.day;


しかし、このクエリだと、例えば5月2日に課金した人が1人もいなかった場合、

...
2016-05-01 1000 1 1
2016-05-02    0 0 1
2016-05-03  500 2 2
...


とはならず、

...
2016-05-01 1000 1 1
2016-05-03  500 2 2
...


と、なってしまうからです。
JOINする順番を逆にしても、同じことです(今度はログインした人が1人もいない日を取得できなくなります)。

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2016/05/16 14:07

    すごく理解しましたー!
    今のところ課金とログインともに空白の日はないので
    こちらを使用させていただきます。

    本当に素人の私に丁寧にありがとうございます!

    キャンセル

関連した質問

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

  • MySQL

    6005questions

    MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

  • SQL

    2472questions

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