指定月に入会と同日に課金をしたユーザーと入会と別の日に課金をしたユーザー数を日付、サービスごとにグループ化して取得したいです。
以下の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
回答1件
あなたの回答
tips
プレビュー