teratail
回答率
85.47%
質問するログイン新規登録
トップMySQLに関する質問mysqlを使って翌日のユーザー数を取得したい

編集履歴

回答編集履歴

4

f

2023/05/06 14:13

投稿

退会済みユーザー
test CHANGED
@@ -1,7 +1,7 @@
1
1
  ```
2
2
  SELECT active_users.date,
3
- COUNT(DISTINCT active_users.user_id) AS active_users,
3
+ COUNT(DISTINCT active_users.user_id) AS active_user,
4
- COUNT(DISTINCT tomorrow_active_users.user_id) AS tomorrow_active_users
4
+ COUNT(DISTINCT tomorrow_active_users.user_id) AS tomorrow_active_user
5
5
  FROM active_users
6
6
  LEFT JOIN active_users AS tomorrow_active_users
7
7
  ON active_users.date = DATE_SUB(tomorrow_active_users.date, INTERVAL 1 DAY)

3

fix

2023/05/06 14:12

投稿

退会済みユーザー
test CHANGED
@@ -4,6 +4,6 @@
4
4
  COUNT(DISTINCT tomorrow_active_users.user_id) AS tomorrow_active_users
5
5
  FROM active_users
6
6
  LEFT JOIN active_users AS tomorrow_active_users
7
- ON active_users.date = DATE_SUB(tomorrow_active_users.date, INTERVAL 1 DAY) AND active_users.user_id = tomorrow_active_users.user_id
7
+ ON active_users.date = DATE_SUB(tomorrow_active_users.date, INTERVAL 1 DAY)
8
8
  GROUP BY active_users.date;
9
9
  ```

2

f

2023/05/06 12:23

投稿

退会済みユーザー
test CHANGED
@@ -1,6 +1,6 @@
1
1
  ```
2
2
  SELECT active_users.date,
3
- COUNT(DISTINCT active_users.user_id) AS active_users,
3
+ COUNT(DISTINCT active_users.user_id) AS active_users,
4
4
  COUNT(DISTINCT tomorrow_active_users.user_id) AS tomorrow_active_users
5
5
  FROM active_users
6
6
  LEFT JOIN active_users AS tomorrow_active_users

1

fix

2023/05/06 12:22

投稿

退会済みユーザー
test CHANGED
@@ -1,7 +1,7 @@
1
1
  ```
2
- SELECT active_users.date AS currentdate,
2
+ SELECT active_users.date,
3
- COUNT(DISTINCT active_users.user_id) AS currentdatecount,
3
+ COUNT(DISTINCT active_users.user_id) AS active_users,
4
- COUNT(DISTINCT tomorrow_active_users.user_id) AS nextdatecount
4
+ COUNT(DISTINCT tomorrow_active_users.user_id) AS tomorrow_active_users
5
5
  FROM active_users
6
6
  LEFT JOIN active_users AS tomorrow_active_users
7
7
  ON active_users.date = DATE_SUB(tomorrow_active_users.date, INTERVAL 1 DAY) AND active_users.user_id = tomorrow_active_users.user_id