Kira group_id tertinggi untuk setiap pengguna sebagai nilai yang berbeza

Question

Terdapat beberapa logik rumit dalam projek terbaru saya (sekurang-kurangnya untuk saya) dan saya telah cuba menggunakan HAVING untuk menyelesaikannya, tetapi semua permainan saya mempunyai keputusan yang salah.

Saya mempunyai jadual terpaut yang menetapkan pengguna kepada kumpulan pengguna dan jadual lain yang merekodkan bilangan pengguna dalam setiap kumpulan jadi saya tidak perlu terus mengira mereka sepanjang masa (jadual kedua dikemas kini apabila pendaftaran pengguna selesai ).

Masalah yang saya hadapi ialah dengan fungsi yang mengemas kini nombor dalam jadual kedua.

Jadual dipautkan kelihatan seperti ini dan berikut ialah data sampel yang menerangkan masalah saya:

+----+---------+----------+
| id | user_id | group_id |
+----+---------+----------+
| 1  | 1       | 1        |
| 2  | 1       | 5        |
| 3  | 14      | 2        |
+----+---------+----------+

Seorang pengguna boleh tergolong dalam mana-mana kumpulan. Malah, untuk menjadi kumpulan, pengguna mesti tergolong dalam semua kumpulan di bawahnya. Ini kerana setiap kumpulan diberikan kebenaran tertentu. Ini tidak boleh diubah tanpa penulisan semula utama aplikasi semasa.

Apa yang saya mahu lakukan ialah mengira kumpulan tertinggi sahaja untuk setiap pengguna, jadi dalam contoh data di atas, pengguna 1 hanya akan dikira dalam kumpulan 5, manakala kumpulan 1 akan kelihatan kosong.

Pada masa ini, semua penyertaan dikira, bermakna jika terdapat 3 orang dalam kumpulan 4, 3 pengguna tersebut juga dikira dalam kumpulan 3, 2, dan 1, yang menjadikan nombor saya agak tidak berguna.

Setakat ini saya telah mencuba beberapa variasi:

SELECT user_group_id, COUNT(user_id) members FROM `group_users` GROUP BY user_group_id HAVING count(user_group_id) = 1;

Ini memberikan saya nombor yang sama. Kemudian saya mencuba:

SELECT user_group_id, COUNT(user_id) members FROM `group_users` GROUP BY user_group_id HAVING COUNT(user_id) = 1;

Tetapi seperti yang saya faham sekarang, saya hanya bertanya kepada pengguna yang tidak muncul dalam mana-mana kumpulan lain.

Saya rasa saya berada di landasan yang betul, tetapi saya tidak tahu di mana atau apa syarat yang sepatutnya. Saya telah melihat beberapa contoh pertanyaan untuk aliran yang serupa, tetapi semua alias hanya menambah kekeliruan.

Bolehkah seseorang menunjukkan saya ke arah yang betul?

Answer 1

Anda boleh menggunakan pengagregatan untuk mendapatkan maksimum group_id 和 COUNT() 窗口函数来对每个返回的最大 group_id untuk setiap pengguna mengira > Bilangan pengguna:

SELECT DISTINCT MAX(group_id) AS group_id,
       COUNT(*) OVER (PARTITION BY MAX(group_id)) AS members
FROM group_users
GROUP BY user_id;

Pertanyaan ini menapis semua group_id ​​yang tidak mempunyai ahli.

Jika anda mahukan hasil untuk semua group_id的结果，请使用表groups的LEFT, gunakan KIRI jadual kumpulan untuk menyertai pertanyaan di atas:

WITH cte AS (
  SELECT DISTINCT MAX(group_id) AS group_id,
         COUNT(*) OVER (PARTITION BY MAX(group_id)) AS members
  FROM group_users
  GROUP BY user_id
)
SELECT g.group_id,
       COALESCE(c.members, 0) AS members
FROM `groups` AS g LEFT JOIN cte AS c
ON c.group_id = g.group_id;

Lihat demo yang dipermudahkan.