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

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

ただいまの
回答率

87.79%

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

解決済

回答 1

投稿 編集

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

score 15

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

以下の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ページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

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

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

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

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

    詳細な説明はこちら

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

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

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

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

  • 退会済みユーザー

    退会済みユーザー

    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 17:44

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

    キャンセル

  • 2018/07/18 18:00

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

    キャンセル

  • 2018/07/18 18:11

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

    キャンセル

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

  • ただいまの回答率 87.79%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

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

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