Блог

Приведенный ниже запрос выдает мне repeated значение для всех дней. Как мне найти 3 верхних repeated значения, которые фактически повторяются в repeated столбце.

Примечание

Мне не нужны top 3 значения из repeated столбца. Я хочу видеть 3 верхних повторяющихся значения в repeated столбце.

 select days, count(*) as repeated from 
(
select 
datediff(day, EnrollDate, InsertDate) as days
from 
(
select 
m.id as memberid,
min(m.EnrollDate) as enrolldate, min(fc.InsertDate) as insertdate
from membersinclude m
join case fc
on m.id = fc.MemberId
and CancelDate < '2019-01-01 00:00:00.000'
and EnrollDate > '2015-01-01 00:00:00.000'
group by m.id
) a
) b
group by days
  

Обновить

 days    mode
19      11
0       10
211     10
42      10
34      10
29      10
35      9
183     9
49      9
  

Если это результат приведенного выше запроса, я хочу, чтобы он возвращался 10, 9, 11 в таком порядке, потому что 10 было больше результатов, чем 9 у которого было больше результатов, чем 11

использовать top ключевое слово

 with cte as
(
select days, count(*) as repeated from 
(
select 
datediff(day, EnrollDate, InsertDate) as days
from 
(
select 
m.id as memberid,
min(m.EnrollDate) as enrolldate, min(fc.InsertDate) as insertdate
from membersinclude m
join case fc
on m.id = fc.MemberId
and CancelDate < '2019-01-01 00:00:00.000'
and EnrollDate > '2015-01-01 00:00:00.000'
group by m.id
) a
) b
group by days
),cte1 as
 ( select mode, count(*) as cnt  from cte
    group by mode 
 ) select top 3 * from cte1
   order by cnt desc
  

 ;WITH cte AS (
    select 
    m.id as memberid,
    DATEDIFF(DAY, MIN(m.EnrollDate), MIN(fc.InsertDate)) AS days
    from membersinclude m
    join [case] fc
    on m.id = fc.MemberId
    and CancelDate < '2019-01-01 00:00:00.000'
    and EnrollDate > '2015-01-01 00:00:00.000'
    group by m.id
),
cte_grouped AS (
    SELECT days, COUNT(*) AS cnt
    FROM cte
    GROUP BY days
),
cte_ranked AS (
    SELECT days, RANK() OVER (ORDER BY cnt desc) rnk
    FROM cte_grouped
)
SELECT TOP(3) days
FROM cte_ranked
ORDER BY rnk
  

С помощью этого примера данных:

 CREATE TABLE membersinclude (id INT, enrolldate DATE, canceldate DATE)
CREATE TABLE [case] (memberid INT, insertdate DATE)

INSERT INTO membersinclude VALUES (1, '1/1/2016', '1/1/2018')
INSERT INTO membersinclude VALUES (2, '1/1/2016', '1/1/2018')
INSERT INTO membersinclude VALUES (3, '1/1/2016', '1/1/2018')
INSERT INTO membersinclude VALUES (4, '1/1/2016', '1/1/2018')
INSERT INTO membersinclude VALUES (5, '1/1/2016', '1/1/2018')
INSERT INTO membersinclude VALUES (6, '1/1/2016', '1/1/2018')
INSERT INTO membersinclude VALUES (7, '1/1/2016', '1/1/2018')
INSERT INTO membersinclude VALUES (8, '1/1/2016', '1/1/2018')
INSERT INTO membersinclude VALUES (9, '1/1/2016', '1/1/2018')
INSERT INTO membersinclude VALUES (10, '1/1/2016', '1/1/2018')

INSERT INTO [CASE] VALUES (1, '2/1/2016')
INSERT INTO [CASE] VALUES (2, '2/1/2016')
INSERT INTO [CASE] VALUES (3, '2/1/2016')
INSERT INTO [CASE] VALUES (4, '2/1/2016')
INSERT INTO [CASE] VALUES (5, '3/1/2016')
INSERT INTO [CASE] VALUES (6, '3/1/2016')
INSERT INTO [CASE] VALUES (7, '3/1/2016')
INSERT INTO [CASE] VALUES (8, '4/1/2016')
INSERT INTO [CASE] VALUES (9, '4/1/2016')
INSERT INTO [CASE] VALUES (10, '5/1/2016')
  

Группирует его в cte_grouped:

 days    cnt
31      4
60      3
91      2
121     1
  

Затем возвращает топ-3, ранжированные по cnt desc:

 days
31
60
91