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

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

ただいまの
回答率

90.34%

  • MySQL

    6203questions

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

  • GROUP BY

    13questions

    GROUP BYとはSQL文のひとつで、SELECT文において特定の列の値が等しい行ごとに表をグループ化します。

MySQLで入会と同日に課金をしたユーザーと入会と別の日に課金をしたユーザー数を日付、サービスごとにグループ化して取得したい

解決済

回答 1

投稿 編集

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

sushippy

score 4

指定月に入会と同日に課金をしたユーザーと入会と別の日に課金をしたユーザー数を日付、サービスごとにグループ化して取得したいです。

以下のSQLを発行すると、入会と同日に課金をしたユーザー数(new_dau)の数値は正しいのですが、入会と別の日に課金をしたユーザー(old_dau)の数値が合いません。

入会と別の日に課金をしたユーザー(old_dau)の数も正しく取得する方法はないでしょうか?

 取得SQL

SELECT
  DATE_FORMAT(payment_date, '%e') day
  , service_type
  , count(reg_date = payment_date or null) new_dau
  , count(reg_date < payment_date or null) old_dau 
FROM
  ( 
    SELECT
      id
      , DATE_FORMAT(m.reg_date, '%Y-%m-%d') reg_date
      , DATE_FORMAT(lc.payment_date, '%Y-%m-%d') payment_date
      , service_type 
    FROM
      dta_member m 
      INNER JOIN ( 
        SELECT
          payment_date
          , member_id 
        FROM
          log_charge 
        WHERE
          status = 1 
          AND payment_date >= '2016-02-01 00:00:00' 
          AND payment_date <= '2016-02-29 23:59:59'
      ) AS lc 
        ON m.id = lc.member_id 
    GROUP BY
      m.id 
    ORDER BY
      payment_date
      , reg_date
  ) AS c 
GROUP BY
  payment_date
  , service_type

テーブル構造

mysql> show create table dta_member;
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                         |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| dta_member | CREATE TABLE `dta_member` (
  `id` int(11) NOT NULL,
  `service_type` int(11) DEFAULT NULL,
  `reg_date` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `reg_date` (`reg_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table log_charge;
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                                                                                                                                                                                        |
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| log_charge | CREATE TABLE `log_charge` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `member_id` int(11) NOT NULL,
  `status` smallint(6) NOT NULL DEFAULT '0' COMMENT '1:完了',
  `payment_date` datetime DEFAULT NULL COMMENT '決済完了日時',
  PRIMARY KEY (`id`),
  KEY `member_id` (`member_id`),
  KEY `payment_date` (`payment_date`)
) ENGINE=InnoDB AUTO_INCREMENT=88 DEFAULT CHARSET=utf8 COMMENT='課金ログ'             |
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

インサート用サンプルSQL
https://drive.google.com/file/d/1xyLRD2wFNuajB2gLJ9F9jlUUyZWpNuwU/view

MySQLバージョン5.5.60

$ mysql --version
mysql  Ver 14.14 Distrib 5.5.60, for Linux (x86_64) using readline 5.1

 取得SQL変更

SELECT
  DATE_FORMAT(payment_date, '%e') day
  , service_type
  , sum(reg_date =  payment_date) new_dau
  , sum(reg_date <  payment_date) old_dau
FROM
(
  SELECT
    id
    , DATE_FORMAT(m.reg_date, '%Y-%m-%d') reg_date
    , DATE_FORMAT(lc.payment_date, '%Y-%m-%d') payment_date
    , service_type 
  FROM
    dta_member m 
    INNER JOIN ( 
      SELECT
        payment_date
        , member_id 
      FROM
        log_charge 
      WHERE
        status = 1 
        AND payment_date >= '2016-02-01 00:00:00' 
        AND payment_date <= '2016-02-29 23:59:59'
    ) AS lc 
      ON m.id = lc.member_id 
  GROUP BY
    m.id
  ORDER BY
    payment_date
    , reg_date
) AS c
GROUP BY
  payment_date
  , service_type

2016-02-10以降のold_dauが0件になる。

 2016-02-29のレコード取得

  SELECT
    id
    , DATE_FORMAT(m.reg_date, '%Y-%m-%d') reg_date
    , DATE_FORMAT(lc.payment_date, '%Y-%m-%d') payment_date
    , service_type 
  FROM
    dta_member m 
    INNER JOIN ( 
      SELECT
        payment_date
        , member_id 
      FROM
        log_charge 
      WHERE
        status = 1 
        AND payment_date >= '2016-02-01 00:00:00' 
        AND payment_date <= '2016-02-29 23:59:59'
    ) AS lc 
      ON m.id = lc.member_id 
  WHERE
    DATE_FORMAT(payment_date, '%Y-%m-%d') = '2016-02-29'
    AND DATE_FORMAT(m.reg_date, '%Y-%m-%d') < DATE_FORMAT(lc.payment_date, '%Y-%m-%d')
  GROUP BY
    m.id
  ORDER BY
    payment_date
    , reg_date
;
183 rows in set (0.02 sec)

183行ある。

 正しく動いたSQL

SELECT
  DATE_FORMAT(payment_date, '%e') day
  , service_type
  , sum(reg_date =  payment_date) new_dau
  , sum(reg_date <  payment_date) old_dau
FROM
(
  SELECT
    distinct id
    , DATE_FORMAT(m.reg_date, '%Y-%m-%d') reg_date
    , DATE_FORMAT(lc.payment_date, '%Y-%m-%d') payment_date
    , service_type 
  FROM
    dta_member m 
    INNER JOIN ( 
      SELECT
        payment_date
        , member_id 
      FROM
        log_charge 
      WHERE
        status = 1 
        AND payment_date >= '2016-02-01 00:00:00' 
        AND payment_date <= '2016-02-29 23:59:59'
    ) AS lc 
      ON m.id = lc.member_id 
  GROUP BY
    m.id
    , payment_date
  ORDER BY
    payment_date
    , reg_date
) AS c
GROUP BY
  payment_date
  , service_type
  • 気になる質問をクリップする

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

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

  • Kosuke_Shibuya

    2018/07/18 12:35

    テーブル構造は、CREATE文で提示願います。また、サンプルデータの投入用INSERT文もお願いします。

    キャンセル

回答 1

checkベストアンサー

+2

パッと見だけど、

  , count(reg_date = payment_date or null) new_dau
  , count(reg_date < payment_date or null) old_dau 


ではなくて、

  , sum(reg_date = payment_date) new_dau
  , sum(reg_date < payment_date) old_dau 


じゃないかな。

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2018/07/18 14:43

    mysqlはtrueを1として扱うのでカウントするときに
    sumで集計するのがベストですね

    キャンセル

  • 2018/07/18 14:49

    COUNT()の方のor Nullは意図が読めませんでした。意味がない気がする。

    キャンセル

  • 2018/07/18 14:54

    nullはcountすると0件になるので、そのへんを意図しているのだと思います。
    falseは0なのでcountでは1件として認識されるので

    count(case when reg_date = payment_date then 1 else null end ) new_dau

    的なことなんじゃないでしょうかね?

    キャンセル

  • 2018/07/18 14:58 編集

    or [項目名] is nullなら分かるんですけど、 or nullの評価って何?って感じです。
    (True or Null) は、多分結果からはTrueなのでしょうけど。
    逆に(False or Null)もfalseなら結局カウントして、(Null or Null)の場合だけカウントされないじゃないかと。

    キャンセル

  • 2018/07/18 16:15

    一応
    select count(100=100 or null) →1
    select count(100=101 or null) →0

    false or nullはnullなんですよね
    実際null or false でもnullになるのでmysqlのnullは強いようです

    だからこそcoalesceなど演算子ではない関数で評価がされるのでしょう
    select coalesce(null,false,1) →0

    キャンセル

  • 2018/07/18 16:24

    評価の優先順は True > Null > False ってことですね。
    3値っぽい順位ですね。

    キャンセル

  • 2018/07/18 16:43

    saziさんに教えていただいた方法で解決しました。
    or nullは付けたらいけなくて、sumを使うべきなんですね。
    ありがとうございます。
    yambejpさんもありがとうございました。助かりました。

    キャンセル

  • 2018/07/18 16:52

    解決してよかったです。
    ただ、解決したって事は
    >false or nullはnull
    じゃないってことになる気がする。

    キャンセル

  • 2018/07/18 17:44

    サンプルSQLのレコード数を増やすと、数が合わなくなってしまいました。。。

    キャンセル

  • 2018/07/18 18:00

    データ起因だと思われるので、CREATE文とサンプルデータと望む結果を質問に追記して下さい。

    キャンセル

  • 2018/07/18 18:11

    saziさん
    説明不足ですみませんでした。
    サブクエリにのGROUP BYに支払日のpayment_dateを入れておらず、会員IDだけでGROUP化したため、日付が後になるほどレコード数が少なくなっていたようです。

    キャンセル

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

  • MySQL

    6203questions

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

  • GROUP BY

    13questions

    GROUP BYとはSQL文のひとつで、SELECT文において特定の列の値が等しい行ごとに表をグループ化します。

  • トップ
  • MySQLに関する質問
  • MySQLで入会と同日に課金をしたユーザーと入会と別の日に課金をしたユーザー数を日付、サービスごとにグループ化して取得したい