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

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

ただいまの
回答率

89.99%

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

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 2,076

Satochan24

score 84

【2016/2/24追記その2】
以前、ここで新規ユーザ数を出してもらった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が多くなってしまいます。

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

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

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

回答 2

checkベストアンサー

0

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

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;

/* ↑2つの積集合 */
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;

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2016/02/24 11:41

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

    キャンセル

  • 2016/02/24 13:37

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

    キャンセル

  • 2016/02/24 13:42

    回答に追記しました

    キャンセル

  • 2016/02/24 14:02

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

    キャンセル

0

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

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

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2016/02/24 11:50

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

    キャンセル

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

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