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

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

新規登録して質問してみよう
ただいま回答率
85.50%
GROUP BY

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

MySQL

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

Q&A

解決済

1回答

421閲覧

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

退会済みユーザー

退会済みユーザー

総合スコア0

GROUP BY

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

MySQL

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

0グッド

0クリップ

投稿2018/07/18 03:33

編集2018/07/18 09:30

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

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

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

取得SQL

sql

1SELECT 2 DATE_FORMAT(payment_date, '%e') day 3 , service_type 4 , count(reg_date = payment_date or null) new_dau 5 , count(reg_date < payment_date or null) old_dau 6FROM 7 ( 8 SELECT 9 id 10 , DATE_FORMAT(m.reg_date, '%Y-%m-%d') reg_date 11 , DATE_FORMAT(lc.payment_date, '%Y-%m-%d') payment_date 12 , service_type 13 FROM 14 dta_member m 15 INNER JOIN ( 16 SELECT 17 payment_date 18 , member_id 19 FROM 20 log_charge 21 WHERE 22 status = 1 23 AND payment_date >= '2016-02-01 00:00:00' 24 AND payment_date <= '2016-02-29 23:59:59' 25 ) AS lc 26 ON m.id = lc.member_id 27 GROUP BY 28 m.id 29 ORDER BY 30 payment_date 31 , reg_date 32 ) AS c 33GROUP BY 34 payment_date 35 , service_type

テーブル構造

sql

1mysql> show create table dta_member; 2+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 3| Table | Create Table | 4+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 5| dta_member | CREATE TABLE `dta_member` ( 6 `id` int(11) NOT NULL, 7 `service_type` int(11) DEFAULT NULL, 8 `reg_date` datetime NOT NULL, 9 PRIMARY KEY (`id`), 10 KEY `reg_date` (`reg_date`) 11) ENGINE=InnoDB DEFAULT CHARSET=utf8 | 12+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 131 row in set (0.00 sec) 14 15mysql> show create table log_charge; 16+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 17| Table | Create Table | 18+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 19| log_charge | CREATE TABLE `log_charge` ( 20 `id` int(11) NOT NULL AUTO_INCREMENT, 21 `member_id` int(11) NOT NULL, 22 `status` smallint(6) NOT NULL DEFAULT '0' COMMENT '1:完了', 23 `payment_date` datetime DEFAULT NULL COMMENT '決済完了日時', 24 PRIMARY KEY (`id`), 25 KEY `member_id` (`member_id`), 26 KEY `payment_date` (`payment_date`) 27) ENGINE=InnoDB AUTO_INCREMENT=88 DEFAULT CHARSET=utf8 COMMENT='課金ログ' | 28+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 291 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変更

sql

1SELECT 2 DATE_FORMAT(payment_date, '%e') day 3 , service_type 4 , sum(reg_date = payment_date) new_dau 5 , sum(reg_date < payment_date) old_dau 6FROM 7( 8 SELECT 9 id 10 , DATE_FORMAT(m.reg_date, '%Y-%m-%d') reg_date 11 , DATE_FORMAT(lc.payment_date, '%Y-%m-%d') payment_date 12 , service_type 13 FROM 14 dta_member m 15 INNER JOIN ( 16 SELECT 17 payment_date 18 , member_id 19 FROM 20 log_charge 21 WHERE 22 status = 1 23 AND payment_date >= '2016-02-01 00:00:00' 24 AND payment_date <= '2016-02-29 23:59:59' 25 ) AS lc 26 ON m.id = lc.member_id 27 GROUP BY 28 m.id 29 ORDER BY 30 payment_date 31 , reg_date 32) AS c 33GROUP BY 34 payment_date 35 , service_type

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

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

sql

1 SELECT 2 id 3 , DATE_FORMAT(m.reg_date, '%Y-%m-%d') reg_date 4 , DATE_FORMAT(lc.payment_date, '%Y-%m-%d') payment_date 5 , service_type 6 FROM 7 dta_member m 8 INNER JOIN ( 9 SELECT 10 payment_date 11 , member_id 12 FROM 13 log_charge 14 WHERE 15 status = 1 16 AND payment_date >= '2016-02-01 00:00:00' 17 AND payment_date <= '2016-02-29 23:59:59' 18 ) AS lc 19 ON m.id = lc.member_id 20 WHERE 21 DATE_FORMAT(payment_date, '%Y-%m-%d') = '2016-02-29' 22 AND DATE_FORMAT(m.reg_date, '%Y-%m-%d') < DATE_FORMAT(lc.payment_date, '%Y-%m-%d') 23 GROUP BY 24 m.id 25 ORDER BY 26 payment_date 27 , reg_date 28; 29183 rows in set (0.02 sec)

183行ある。

正しく動いたSQL

sql

1SELECT 2 DATE_FORMAT(payment_date, '%e') day 3 , service_type 4 , sum(reg_date = payment_date) new_dau 5 , sum(reg_date < payment_date) old_dau 6FROM 7( 8 SELECT 9 distinct id 10 , DATE_FORMAT(m.reg_date, '%Y-%m-%d') reg_date 11 , DATE_FORMAT(lc.payment_date, '%Y-%m-%d') payment_date 12 , service_type 13 FROM 14 dta_member m 15 INNER JOIN ( 16 SELECT 17 payment_date 18 , member_id 19 FROM 20 log_charge 21 WHERE 22 status = 1 23 AND payment_date >= '2016-02-01 00:00:00' 24 AND payment_date <= '2016-02-29 23:59:59' 25 ) AS lc 26 ON m.id = lc.member_id 27 GROUP BY 28 m.id 29 , payment_date 30 ORDER BY 31 payment_date 32 , reg_date 33) AS c 34GROUP BY 35 payment_date 36 , service_type

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

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

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

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

退会済みユーザー

退会済みユーザー

2018/07/18 03:35

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

回答1

0

ベストアンサー

パッと見だけど、

SQL

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

ではなくて、

SQL

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

じゃないかな。

投稿2018/07/18 03:56

sazi

総合スコア25138

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

yambejp

2018/07/18 05:43

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

2018/07/18 05:49

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

2018/07/18 05:54

nullはcountすると0件になるので、そのへんを意図しているのだと思います。 falseは0なのでcountでは1件として認識されるので count(case when reg_date = payment_date then 1 else null end ) new_dau 的なことなんじゃないでしょうかね?
sazi

2018/07/18 07:23 編集

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

2018/07/18 07: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
sazi

2018/07/18 07:24

評価の優先順は True > Null > False ってことですね。 3値っぽい順位ですね。
退会済みユーザー

退会済みユーザー

2018/07/18 07:43

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

2018/07/18 07:52

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

退会済みユーザー

2018/07/18 08:44

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

2018/07/18 09:00

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

退会済みユーザー

2018/07/18 09:11

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

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問