サブクエリを用いてユーザー数の累積合計を求めたい

Question

###前提・実現したいこと
MySQLユーザー数の累積合計を求めたいです。

###発生している問題・エラーメッセージ
Table 'database_name.e' doesn't exist

###該当のソースコード

SELECT
  E.date,
  E.count,
  (
   SELECT SUM(b.count) FROM E b
   WHERE b.date <= E.date
  ) AS total_counts
FROM
 ( 
  SELECT
    DATE(created_at) date,
    COUNT(*) count
  FROM
    users
  WHERE
    created_at BETWEEN '2017-03-11' AND curdate()
  GROUP BY
    date
  ORDER BY
    date ASC
 ) AS E
ORDER BY
  E.date

/* サブクエリ */
SELECT
  DATE(created_at) date,
  COUNT(*) count
FROM
  users
WHERE
  created_at BETWEEN '2017-03-11' AND curdate()
GROUP BY
  date
ORDER BY
  date ASC
;

/*
date                count
----------           -----
2017-03-11	200
2017-03-12	433
2017-03-13	333
2017-03-14	855
2017-03-15	373
2017-03-16	478
2017-03-17	788
2017-03-18	816
2017-03-19	168
2017-03-20	940
2017-03-21	406
2017-03-22	939
*/

###補足情報(言語/FW/ツール等のバージョンなど)
mysql

よろしくお願いいたします。

Answer 1

SELECT
  DATE(created_at) date,
  COUNT(*) count,
  (SELECT COUNT(*) FROM users AS F WHERE F.created_at >= '2017-03-11' AND F.created_at < ADDDATE(date, 1)) total_count
FROM
  users
WHERE
  created_at BETWEEN '2017-03-11' AND NOW()
GROUP BY
  date
ORDER BY
  date ASC
;

実行結果

mysql> SELECT created_at FROM users ORDER BY created_at;
+---------------------+
| created_at          |
+---------------------+
| 2017-03-10 23:59:59 |
| 2017-03-11 14:01:51 |
| 2017-03-11 14:30:15 |
| 2017-03-12 09:50:06 |
| 2017-03-12 13:00:35 |
| 2017-03-12 16:07:38 |
| 2017-03-13 13:22:40 |
| 2017-03-14 07:08:08 |
| 2017-03-15 09:43:30 |
| 2017-03-16 07:59:23 |
| 2017-03-16 11:18:16 |
| 2017-03-16 22:25:10 |
| 2017-03-17 21:50:36 |
| 2017-03-18 04:24:20 |
| 2017-03-18 11:13:47 |
| 2017-03-18 18:30:28 |
| 2017-03-20 02:38:39 |
| 2017-03-20 17:44:32 |
| 2017-03-20 22:17:21 |
| 2017-03-22 03:31:54 |
| 2017-03-22 20:38:54 |
| 2017-03-23 00:00:00 |
| 2017-03-23 00:00:01 |
+---------------------+
23 rows in set (0.00 sec)

mysql> SELECT
    ->   DATE(created_at) date,
    ->   COUNT(*) count,
    ->   (SELECT COUNT(*) FROM users AS F WHERE F.created_at >= '2017-03-11' AND F.created_at < ADDDATE(date, 1)) total_count
    -> FROM
    ->   users
    -> WHERE
    ->   created_at BETWEEN '2017-03-11' AND NOW()
    -> GROUP BY
    ->   date
    -> ORDER BY
    ->   date ASC
    -> ;
+------------+-------+-------------+
| date       | count | total_count |
+------------+-------+-------------+
| 2017-03-11 |     2 |           2 |
| 2017-03-12 |     3 |           5 |
| 2017-03-13 |     1 |           6 |
| 2017-03-14 |     1 |           7 |
| 2017-03-15 |     1 |           8 |
| 2017-03-16 |     3 |          11 |
| 2017-03-17 |     1 |          12 |
| 2017-03-18 |     3 |          15 |
| 2017-03-20 |     3 |          18 |
| 2017-03-22 |     2 |          20 |
| 2017-03-23 |     2 |          22 |
+------------+-------+-------------+
11 rows in set (0.00 sec)

ちなみに、ご質問文のサブクエリでは、WHERE句を

WHERE
  created_at BETWEEN '2017-03-11' AND curdate()

としておりますが、これを2017年03月23日に実行すると

'2017-03-11 00:00:00' <= created_at <= '2017-03-23 00:00:00'

という意味になり、当日分のデータが正しく取れません。

mysql> SELECT
    ->   DATE(created_at) date,
    ->   COUNT(*) count,
    ->   (SELECT COUNT(*) FROM users AS F WHERE F.created_at >= '2017-03-11' AND F.created_at < ADDDATE(date, 1)) total_count
    -> FROM
    ->   users
    -> WHERE
    ->   created_at BETWEEN '2017-03-11' AND curdate()
    -> GROUP BY
    ->   date
    -> ORDER BY
    ->   date ASC
    -> ;
+------------+-------+-------------+
| date       | count | total_count |
+------------+-------+-------------+
| 2017-03-11 |     2 |           2 |
| 2017-03-12 |     3 |           5 |
| 2017-03-13 |     1 |           6 |
| 2017-03-14 |     1 |           7 |
| 2017-03-15 |     1 |           8 |
| 2017-03-16 |     3 |          11 |
| 2017-03-17 |     1 |          12 |
| 2017-03-18 |     3 |          15 |
| 2017-03-20 |     3 |          18 |
| 2017-03-22 |     2 |          20 |
| 2017-03-23 |     1 |          22 | ※ ここの count の数がおかしい
+------------+-------+-------------+
11 rows in set (0.00 sec)

Comments

[hesato]

ありがとうございます!
求めているものでございました。

> created_at BETWEEN '2017-03-11' AND NOW()

最後のcountの訂正、気が付きませんでした。
ありがとうございます。

[KiyoshiMotoki]

b

Answer 2

レコードのレイアウトとtotal_counts理解ができていませんので想像です。

SELECT
  E.date,
  E.count,
  (
   SELECT count(b.created_at) FROM users b
   WHERE b.created_at BETWEEN '2017-03-11' and E.date
  ) AS total_counts
FROM
 (
  SELECT
    DATE(created_at) date,
    COUNT(*) count
  FROM
    users
  WHERE
    created_at BETWEEN '2017-03-11' AND curdate()
  GROUP BY
    date
  ORDER BY
    date ASC
 ) AS E
ORDER BY
  E.date

Comments

[hesato]

ありがとうございます。
恥ずかしながら,サブクエリはfromでしか呼び出せないものと思い込んでおりました。
selectでも呼び出せるのですね、勉強になりました。

ありがとうございます!

Answer 3

サブクエリをSELECT構文から読んでいるからじゃないですかね？
おもいきってEに当たる部分をviewにしてみてはどうでしょうか？

ちなみにcreated_atはdatetimeですか？
日付をつかったbetweenを取ると思った通りの期間が得られないこともあるので
注意してください

追記

created_atをなぜ日付化しているか仕様がかいていないのでわかりません
かりにcreated_atがdate型だとすると以下

select created_at,count(*) as c,
(select count(*) from users as t2 where created_at BETWEEN '2017-03-11' AND curdate()
AND t1.created_at>=t2.created_at
) as t
from users as t1
where created_at BETWEEN '2017-03-11' AND curdate()
group by created_at

Comments

[hesato]

ご回答,と注意事項をありがとうございます。
viewを用いてやってみます!

[hesato]

localでは'create view'を実行できますが、業務用のdatabaseでは権限がないため実行できず,,,
他に方法はあるでしょうか?><

[yambejp]

一応追記しておきました
仕様はちゃんと書いてください

もしcreated_atがdatetime型だとして、日付で集計する前提であれば
created_date的なカラムをつくっておくことをお勧めします

[yambejp]

さらに言えば、サマリー用のテーブルを作っておくのが吉
日付は遡って更新されることはないでしょうから、
日締め処理で夜中に、前日のデータを集計してサマリーテーブルに
保持しておけば、データが増えても高速に集計結果を得られます

[hesato]

仕様に気をつけます。 ありがとうございます。

> サマリー用のテーブルを作っておく

なるほど...ありがとうございます><

追記

created_at を、 DATE(created_at)にし、datetime型としました。