Строки фильтра SQL основаны без использования Group by

Question

У меня есть запрос, который будет выполнять объединения более 6 таблиц и извлекает различные столбцы в зависимости от условия. Я хочу добавить дополнительное условие фильтра, которое даст мне только тех участников, которые имеют count(distinct dateCaptured)>30, Я могу получить список участников, которые удовлетворяют этому условию, используя Group by а также having, Но я не хочу группировать по другим именам столбцов из-за этого одного условия. Нужно ли использовать PARTITION BY в этом случае.

Образец ТАБЛИЦА А

+-----+------------+--------------+
| Id  | Identifier | DateCaptured |
+-----+------------+--------------+
| 1   |      05548 | 2017-09-01   |
| 2   |      05548 | 2017-09-01   |
| 3   |      05548 | 2017-09-01   |
| 4   |      05548 | 2017-09-02   |
| 5   |      05548 | 2017-09-03   |
| 6   |      05548 | 2017-09-04   |
| 7   |      37348 | 2017-08-15   |
| 8   |      37348 | 2017-08-15   |
| .   |            |              |
| .   |            |              |
| .   |            |              |
| 54  |      37348 | 2017-10-15   |
+-----+------------+--------------+

запрос

SELECT  a.value,
        b.value, c.value,
        d.value
        FROM Table a
    INNER JOIN Table b on a.Id=b.id
    INNER JOIN Table c on a.Id=c.Id and s.Invalid=0
    INNER JOIN Table d on a.Id=d.Id 

Предположим, что таблица а содержит более 30 записей для идентификатора 37348, Как я могу получить только этот идентификатор для вышеуказанного запроса.

Это пациенты, которые меня интересуют для вышеупомянутого SELECT.

SELECT  a.Identifier,count(DISTINCT DateCaptured)
    FROM Table a
    INNER JOIN Table b on a.Id=b.id
    INNER JOIN Table c on a.Id=c.Id and s.Invalid=0
    INNER JOIN Table d on a.Id=d.Id 
    GROUP BY Identifier
    HAVING count(DISTINCT DateCaptured)>30

Answer 1

WITH cte as (
    SELECT  a.Identifier
    FROM Table a
    INNER JOIN Table b on a.Id=b.id
    INNER JOIN Table c on a.Id=c.Id and s.Invalid=0
    INNER JOIN Table d on a.Id=d.Id 
    GROUP BY Identifier
    HAVING count(DISTINCT DateCaptured) > 30
)
SELECT  a.value,
        b.value, c.value,
        d.value
FROM Table a
INNER JOIN Table b on a.Id=b.id
INNER JOIN Table c on a.Id=c.Id and s.Invalid=0
INNER JOIN Table d on a.Id=d.Id 
INNER JOIN cte on cte.Identifier = a.Identifier

Answer 2

Если несколько строк действительно находятся в tableAтогда вы можете сделать:

SELECT a.value, b.value, c.value, d.value
FROM (SELECT a.*, COUNT(*) OVER (PARTITION BY id) as cnt
      FROM a
     ) a INNER JOIN
     b 
     ON a.Id = b.id INNER JOIN 
     c 
     ON a.Id = c.Id AND s.Invalid = 0 INNER JOIN 
     d 
     ON a.Id = d.Id
WHERE a.cnt > 30;

Примечание: если вам все еще нужно count(distinct) ты можешь сделать:

SELECT a.value, b.value, c.value, d.value
FROM (SELECT a.*, SUM(CASE WHEN seqnum = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY id) as cnt
      FROM (SELECT a.*, ROW_NUMBER() OVER (PARTITION BY id ORDER BY DateCaptured) as seqnum
            FROM a
           ) a
     ) a INNER JOIN
     b 
     ON a.Id = b.id INNER JOIN 
     c 
     ON a.Id = c.Id AND s.Invalid = 0 INNER JOIN 
     d 
     ON a.Id = d.Id
WHERE a.cnt > 30;

Answer 3

SELECT  a.value,
        b.value, c.value,
        d.value
        FROM Table a
    INNER JOIN Table b on a.Id=b.id
    INNER JOIN Table c on a.Id=c.Id and s.Invalid=0
    INNER JOIN Table d on a.Id=d.Id 
WHERE a.Identifier IN (SELECT  a1.Identifier  
    FROM Table a1
    GROUP BY a1.Identifier HAVING count(DISTINCT a1.DateCaptured)>30)