مقدمه
Mode در آمار به این شکل تعریف می شود:
مقداری که بیشترین بار ظاهر شده است. مثلا در مجموعه
{2و4و6و7و5و4و2و4و4} عدد 4 بیشترین تکرار را در این مجموعه دارد و به عنوان
Mode شناخته می شود.
در SQL Server
تابع تجمعی برای این منظور در نظر گرفته نشده است. بنابراین ناچارا باید توسط
Query این مشکل را بر طرف کنیم.
مساله
جدولی را در نظر بگیرین که شامل چندین
گروه بوده و در هر گروه چندین مقدار وجود دارد. هدف ما پیدا کردن Mode
هر مجموعه همراه با تعداد تکرار مقدار می باشد.
داده های اولیه و خام و نتیجه مطلوب
حاصل شده توسط query مناسب:
Before
Querying:
grp val
---------- -----------
1 5
1 3
1 3
1 87
1 24
1 5
1 3
2 13
2 13
2 14
2 54
3 1
3 8
3 1
After
Querying:
grp val
cnt
----------- ----------- -----------
1 3 3
2 13 2
3 1 2
CREATE
TABLE Mode
(grp
INT NOT
NULL,
val INT NOT
NULL);
INSERT
INTO Mode SELECT
1, 5;
INSERT
INTO Mode SELECT
1, 3;
INSERT
INTO Mode SELECT
1, 3;
INSERT
INTO Mode SELECT
1, 87;
INSERT
INTO Mode SELECT
1, 24;
INSERT
INTO Mode SELECT
1, 5;
INSERT
INTO Mode SELECT
1, 3;
INSERT
INTO Mode SELECT
2, 13;
INSERT
INTO Mode SELECT
2, 13;
INSERT
INTO Mode SELECT
2, 14;
INSERT
INTO Mode SELECT
2, 54;
INSERT
INTO Mode SELECT
3, 1;
INSERT
INTO Mode SELECT
3, 8;
INSERT
INTO Mode SELECT
3, 1;
راه حل ها
راه حل اول:
WITH
C AS
(
SELECT grp,
val,
COUNT(*)
AS cnt,
RANK()
OVER(PARTITION
BY grp
ORDER
BY COUNT(*)
DESC)
AS rn
FROM Mode
GROUP BY grp,
val
)
SELECT grp,
val, cnt
FROM C
WHERE rn = 1;
راه حل دوم:
WITH
C1(grp, val,
cnt) AS
(
SELECT grp,
val, COUNT(*)
FROM Mode
GROUP BY val,
grp
), C2(grp,
maxim) AS
(
SELECT grp,
MAX(cnt)
FROM C1
GROUP BY grp
)
SELECT C1.*
FROM C1
INNER JOIN
C2
ON C1.grp
= C2.grp
AND C1.cnt
= C2.maxim;