【MySQL】累積度数のグラフ作成のために、毎月の累積数を一度に取得したい。

Question

【2016/2/24追記その２】
以前、ここで新規ユーザ数を出してもらったSQLが下記のようにあり、このSQLと、先程の追記のSQLを
結合できれば、できそうですが...苦戦しています。

【毎月の新規ユーザのみ】

SELECT (
a.access_month
) AS access, b.newer_count AS newer
FROM (

SELECT access_month, count( * ) AS all_count
FROM (

SELECT DISTINCT user_id, date_format( access_day, '%Y年%m月' ) AS access_month
FROM access_record
WHERE access_type =101
) AS c
GROUP BY access_month)a
LEFT JOIN (

SELECT access_month, count( * ) AS newer_count
FROM (

SELECT user_id, min( date_format( access_day, '%Y年%m月' ) ) access_month
FROM access_record
GROUP BY user_id
) AS d
GROUP BY access_month)b ON a.access_month = b.access_month
ORDER BY `a`.`access_month` DESC
LIMIT 12  

【2016/2/24追記】
現在、下記のSQLまで作成し、最新月を先頭にし、1年間表示に絞り、完成に近い状態なのですが、
できれば、累積の新規ユーザ数の隣に、その月の新規ユーザ数も表示させたいと思います。
別の表を結合するしかないかなぁと考えていますが…
何か方法ありますでしょうか？

【累積の新規ユーザ数】

SELECT date_format( M.last_date, '%Y年%m月' ) AS mon, count( * ) AS cnt
FROM (

SELECT DISTINCT last_day( access_day ) AS last_date
FROM access_record
) AS M
JOIN (

SELECT user_id, min( access_day ) AS user_date
FROM access_record
GROUP BY user_id
) AS U
WHERE M.last_date >= U.user_date
GROUP BY M.last_date
ORDER BY mon DESC 
LIMIT 12

【以下、元々の質問】
アプリの新規の使用ユーザーを累積度数でグラフ化したいと
思いまして、その値を取得するSQLを考えています。
以下の通り、部分的には取得できています。

【全新規ユーザ数】

SELECT COUNT( * ) 
FROM (

SELECT user_id, min( date_format( access_day, '%Y-%m' ) ) access_month, access_day
FROM access_record
GROUP BY user_id
) AS a  

また、今月の増加した新規ユーザ数は、

SELECT COUNT( * ) 
FROM (

SELECT user_id, min( date_format( access_day, '%Y-%m' ) ) access_month, access_day
FROM access_record
GROUP BY user_id
) AS a
WHERE date_format( access_day, '%Y-%m' ) >= date_format( DATE_SUB( NOW( ) , INTERVAL 1 
MONTH ) , '%Y-%m' ) 
AND date_format( access_day, '%Y-%m' ) < date_format( NOW( ) , '%Y-%m' )

先月に増加した新規ユーザ数は

SELECT COUNT( * ) 
FROM (

SELECT user_id, min( date_format( access_day, '%Y-%m' ) ) access_month, access_day
FROM access_record
GROUP BY user_id
) AS a
WHERE date_format( access_day, '%Y-%m' ) >= date_format( DATE_SUB( NOW( ) , INTERVAL 2 
MONTH ) , '%Y-%m' ) 
AND date_format( access_day, '%Y-%m' ) < date_format( DATE_SUB( NOW( ) , INTERVAL 1 
MONTH ) , '%Y-%m' ) 

で得られました。あとは、変数に入れて引き算してやれば
累積度数の値は得られると思うのですが、
1年分作成使用とした場合、SQLが多くなってしまいます。

何か、１つのSQLで1年分取得する方法はありますでしょうか？
最新月のグラフの値は、常に、アクセスのカウント取得開始してからの累積値にしたいと思っています。

Answer 1

下記でそれっぽいものが出るかもしれません。

select date_format(M.last_date, '%Y/%m') as mon, count(*) as cnt from (
  select distinct last_day(access_day) as last_date from access_record
) as M
 join (
    select user_id, min(access_day) as user_date from access_record group by user_id
) as U
where M.last_date >= U.user_date
group by M.last_date;

↑のSQLまで至った経緯。

/* 月末の一覧 */
select distinct last_day(access_day) as last_date from access_record;

/* ユーザーごとの最初のアクセス日 */
select user_id, min(access_day) as user_date from access_record group by user_id;

/* ↑２つの積集合 */
select * from (
  select distinct last_day(access_day) as last_date from access_record
) as M
 join (
    select user_id, min(access_day) as user_date from access_record group by user_id
) as U
order by M.last_date, U.user_date;

/* ↑の集合から「ユーザーのアクセス日」が「各月末の日付」よりも小さいものを除外 */
select * from (
  select distinct last_day(access_day) as last_date from access_record
) as M
 join (
    select user_id, min(access_day) as user_date from access_record group by user_id
) as U
where M.last_date >= U.user_date
order by M.last_date, U.user_date;

/* ↑の結果を月末の日付でグループ化 */
select date_format(M.last_date, '%Y/%m') as mon, count(*) as cnt from (
  select distinct last_day(access_day) as last_date from access_record
) as M
 join (
    select user_id, min(access_day) as user_date from access_record group by user_id
) as U
where M.last_date >= U.user_date
group by M.last_date;

---

累積の新規ユーザ数の左隣りに、その月の新規ユーザ数も表示させたいのですが

select
  date_format(M.last_date, '%Y/%m') as mon,
  count(*) as tcnt,
  sum(last_day(M.last_date) = last_day(U.user_date)) as mcnt
from (
  select distinct last_day(access_day) as last_date from access_record
) as M
 join (
    select user_id, min(access_day) as user_date from access_record group by user_id
) as U
where M.last_date >= U.user_date
group by M.last_date;

Comments

[Satochan24]

回答有難うございました。
なぜ、最初の短いSQLで取得できるのか、まだあまり理解できていませんが、
欲しかった情報は得られていました。
最終的にどうするか。PHPの出力部分完了したら、解決処理します。

[Satochan24]

度々、すみません。質問にも追記したのですが、
累積の新規ユーザ数の左隣りに、その月の新規ユーザ数も表示させたいのですが、
何か方法等ありますでしょうか？別の表を結合させる必要があるかなぁと思っていますが…

[ngyuki]

回答に追記しました

[Satochan24]

早速の回答有難うございます。
すごい！こんな短いSQLで表示できるとは思いませんでした。
有難うございました。

Answer 2

SQLの負荷は考慮していません。

SELECT
	YM,
	(SELECT COUNT(DISTINCT S.user_id)
	FROM access_record S
	WHERE S.access_day < date_format(A.access_day, '%Y-%m-1')) + COUNT(*) AS CNT
FROM (
SELECT
	user_id,
	date_format(MIN(access_day), '%Y-%m' ) AS YM,
	access_day
FROM access_record
GROUP BY user_id) A
GROUP BY YM
ORDER BY YM DESC
LIMIT 12

意図している結果になるかご確認ください。

Comments

[Satochan24]

回答有難うございました。
欲しい結果が得られていました。
ただ、新規ユーザーがいない月は、その月が表示されなかったので、
表示されたほうがいいかなぁと思いました。