MySQLでグループ化した上で、特定カラムの値が同じかどうかを判定したい

Question

次のようなテーブルがあります

SELECT id, alive, open_at FROM schedules;
+----+-------+---------------------+
| id | alive | open_at             |
+----+-------+---------------------+
|  1 |     1 | 2019-11-01 00:00:00 |
|  2 |     0 | 2019-11-30 00:00:00 |
|  3 |     1 | 2019-12-01 00:00:00 |
|  4 |     1 | 2019-12-31 00:00:00 |
|  5 |     0 | 2020-01-01 00:00:00 |
|  5 |     0 | 2020-01-31 00:00:00 |
+----+-------+---------------------+

これらを年別にグループ化すると次のようになります

SELECT
  DATE_FORMAT(open_at, '%Y') AS op_year,
  DATE_FORMAT(open_at, '%c') AS op_month
FROM schedules
  GROUP BY (DATE_FORMAT(open_at, '%Y-%m'))
  HAVING op_year = 2019
;

+---------+----------+
| op_year | op_month |
+---------+----------+
| 2019    | 11       |
| 2019    | 12       |
| 2020    | 1        |
+---------+----------+

今回したいことは、年別にグループ化した上で、
そのグループにあるレコードのaliveの値が、全て1であるか否かを取得することです。

次のような形でレコードを取得したいです

SELECT
  (なにかCASE文を使う?) AS alives,
  DATE_FORMAT(open_at, '%Y') AS op_year,
  DATE_FORMAT(open_at, '%c') AS op_month
FROM schedules
  GROUP BY (DATE_FORMAT(open_at, '%Y-%m'))
  HAVING op_year = 2019
;

+--------+---------+----------+
| alives | op_year | op_month |
+--------+---------+----------+
| 0      | 2019    | 11       |
| 1      | 2019    | 12       |
| 0      | 2020    | 1        |
+--------+---------+----------+

このような形でレコードを取得するには、どのようなSQL文を発行すればいいのでしょうか？

Answer 1

aliveが数値で0と1という前提で、以下でどうでしょう。

SELECT
  case when sum(alive)=count(*) then 1 else 0 end AS alives,
  DATE_FORMAT(open_at, '%Y') AS op_year,
  DATE_FORMAT(open_at, '%c') AS op_month
FROM schedules
  GROUP BY (DATE_FORMAT(open_at, '%Y-%m'))

Answer 2

「なにかCASE文を使う?」
⇒この形が分かりやすくて良いと思います。

「全て1であるか否かを取得する」であれば、
kasa0様の内容([s]の有無の違いはあります）、
Kosuke_Shibuya様の内容（case文で実現する場合でもヒントになるかと思います）で良いかと思います。

また、少し変えて、以下の内容でも大丈夫です。

(
 CASE
  WHEN max( alive ) = 1 AND COUNT( distinct alive ) = 1 THEN max( alive )
  ELSE 0
 END
) as alives

以下、他の方法のおまけ回答です。

「特定カラムの値が全て同じかどうか」という場合、
max(alives)=min(alives)　に違和感があるのであれば、
以下のような方法もあります。

(
 CASE
  WHEN COUNT('X') = SUM( CASE WHEN alive = 1 THEN 1 ELSE 0 END ) THEN 1
  ELSE 0
 END
) as alives

Comments

[qwe001]

丁寧なご回答誠にありがとうございます。こちらのやり方で期待した通りにできました。

Answer 3

SELECT
  COUNT(distinct `alive`) = 1 and MAX(distinct `alive`) = 1 and MIN(distinct `alive`) = 1 as alive,
  -- COUNT(distinct `alive`),
  -- MAX(distinct `alive`),
  -- MIN(distinct `alive`),
  YEAR(open_at) AS op_year,
  MONTH(open_at) AS op_month
FROM schedules
  GROUP BY (DATE_FORMAT(open_at, '%Y-%m'))
  HAVING op_year = 2019

Answer 4

良い方法が思いつかなかった。

CASE WHEN max(alives)=min(alives) THEN max(alives)
ELSE 0
END AS alives

Answer 5

SELECT
  MAX(alives） AS alives,
  DATE_FORMAT(open_at, '%Y') AS op_year,
  DATE_FORMAT(open_at, '%c') AS op_month
FROM schedules
  GROUP BY (DATE_FORMAT(open_at, '%Y-%m'))
--  HAVING op_year = 2019
WHERE alives = 1;

では駄目でしょうか？

Comments

[nandymak]

alives=0の行も必要ですかね。
alivesの合計を件数で割って、小数点以下は切り捨てればいけるかも。
その時はWHERE alives = 1は不要です。

TRUNCATE(SUM(alives)/COUNT(alives),0)
平均をTRUNCATEでも良いかも。