Выбор COUNT из разных критериев таблицы

У меня есть таблица с именем "jobs". Для конкретного пользователя работа может быть активной, архивированной, просроченной, ожидающей или закрытой. Прямо сейчас каждый запрос страницы генерирует 5 запросов COUNT, и в попытке оптимизации я пытаюсь уменьшить это до одного запроса. Это то, что у меня есть до сих пор, но это едва быстрее, чем 5 индивидуальных запросов. Обратите внимание, что я упростил условия для каждого подзапроса, чтобы упростить его понимание, однако полный запрос действует одинаково.

Есть ли способ получить эти 5 подсчетов в одном запросе без использования неэффективных подзапросов?

SELECT
  (SELECT count(*)
    FROM "jobs"
    WHERE
      jobs.creator_id = 5 AND
      jobs.status_id NOT IN (8,3,11) /* 8,3,11 being 'inactive' related statuses */
  ) AS active_count, 
  (SELECT count(*)
    FROM "jobs"
    WHERE
      jobs.creator_id = 5 AND
      jobs.due_date < '2011-06-14' AND
      jobs.status_id NOT IN(8,11,5,3) /* Grabs the overdue active jobs
                                      ('5' means completed successfully) */
  ) AS overdue_count,
  (SELECT count(*)
    FROM "jobs"
    WHERE
      jobs.creator_id = 5 AND
      jobs.due_date BETWEEN '2011-06-14' AND '2011-06-15 06:00:00.000000'
  ) AS due_today_count

Это продолжается для еще двух подзапросов, но я думаю, что вы поняли эту идею.

Есть ли более простой способ для сбора этих данных, так как в основном 5 разных COUNT отключены из одного и того же подмножества данных из таблицы заданий?

Подмножество данных - "creator_id = 5", после чего каждый счет в основном составляет всего 1-2 дополнительных условия. Обратите внимание: прямо сейчас мы используем Postgres, но в ближайшем будущем можем перейти к MySQL. Поэтому, если вы можете предоставить ANSI-совместимое решение, я был бы благодарен:)

Ответы

Ответ 1

Это типичное решение. Используйте оператор case, чтобы разбить различные условия. Если запись соответствует, она получает 1 else a 0. Затем выполните SUM значения

  SELECT
    SUM(active_count) active_count,
    SUM(overdue_count) overdue_count
    SUM(due_today_count) due_today_count
  FROM 
  (

  SELECT 
    CASE WHEN jobs.status_id NOT IN (8,3,11) THEN 1 ELSE 0 END active_count,
    CASE WHEN jobs.due_date < '2011-06-14' AND jobs.status_id NOT IN(8,11,5,3)  THEN 1 ELSE 0 END  overdue_count,
    CASE WHEN jobs.due_date BETWEEN '2011-06-14' AND '2011-06-15 06:00:00.000000' THEN 1 ELSE 0 END  due_today_count

    FROM "jobs"
    WHERE
      jobs.creator_id = 5 ) t

UPDATE Как отмечено, когда 0 записей возвращаются как t, этот результат получается как единственный результат Nulls во всех значениях. У вас есть три варианта

1) Добавьте предложение A, чтобы у вас было не возвращено записей, а не результат всех NULLS

   HAVING SUM(active_count) is not null

2) Если вы хотите, чтобы все нули были возвращены, вы могли бы добавить coalesce ко всем вашим суммам

Например

 SELECT
      COALESCE(SUM(active_count)) active_count,
       COALESCE(SUM(overdue_count)) overdue_count
      COALESCE(SUM(due_today_count)) due_today_count

3) Воспользуйтесь тем, что COUNT(NULL) = 0 как продемонстрировано сбарро. Следует отметить, что значение, отличное от нуля, может быть любым, что не должно быть 1

например

 SELECT
      COUNT(CASE WHEN 
            jobs.status_id NOT IN (8,3,11) THEN 'Manticores Rock' ELSE NULL
       END) as [active_count]

Ответ 2

Я бы использовал этот подход, используя COUNT в сочетании с CASE WHEN.

SELECT 
    COUNT(CASE WHEN 
        jobs.status_id NOT IN (8,3,11) THEN 1 
    END) as [Count1],
    COUNT(CASE WHEN 
        jobs.due_date < '2011-06-14' 
        AND jobs.status_id NOT IN(8,11,5,3) THEN 1
    END) as [COUNT2],
    COUNT(CASE WHEN
            jobs.due_date BETWEEN '2011-06-14' AND '2011-06-15 06:00:00.000000'
    END) as [COUNT3]
FROM 
    "jobs"
WHERE 
     jobs.creator_id = 5

Ответ 3

Краткое

В SQL Server 2012 появилась логическая функция IIF. Используя SQL Server 2012 или выше, вы теперь можете использовать эту новую функцию вместо выражения CASE. Функция IIF также работает с Azure SQL Database (но на данный момент она не работает с Azure SQL Data Warehouse или Parallel Data Warehouse). Он сокращает выражение CASE.

Я использую функцию IIF, а не выражение CASE, когда есть только один случай. Это облегчает боль от необходимости писать CASE WHEN condition THEN x ELSE y END и вместо этого записывать его как IIF(condition, x, y). Если несколько условий могут быть выполнены (несколько WHEN s), вы должны вместо этого использовать регулярное выражение CASE, а не вложенные функции IIF.

Возвращает одно из двух значений, в зависимости от того, является ли булево выражение оценивает значение true или false в SQL Server.

Синтаксис
IIF ( boolean_expression, true_value, false_value )
Аргументы

boolean_expression
Действительное булево выражение.

Если этот аргумент не является булевым выражением, то синтаксическая ошибка поднят.

true_value
Значение для возврата, если boolean_expression оценивается как правда.

false_value
Значение, возвращаемое, если boolean_expression оценивает на false.

Примечания

IIF является сокращенным способом для записи выражения CASE. Он оценивает булево выражение передается как первый аргумент, а затем возвращается либо из двух других аргументов, основанных на результате оценка. То есть, true_value возвращается, если булев выражение истинно, а false_value возвращается, если булев выражение ложно или неизвестно. true_value и false_value могут быть любого типа. Те же правила, которые применяются к выражению CASE для Булевы выражения, нулевое обращение и возвращаемые типы также применяются к IIF. Для получения дополнительной информации см. CASE (Transact-SQL).

Тот факт, что IIF переведен в CASE, также влияет на другие аспекты поведения этой функции. Поскольку CASEвыражения могут быть вложены только до уровня 10, IIF операторов также может быть вложен только до максимального уровня 10. Кроме того, IIFудалены на другие серверы как семантически эквивалентные CASEвыражение со всеми поведением удалённого выражения CASE.

код

Реализация функции IIF в SQL будет напоминать следующее (используя ту же логику, представленную @rsbarro в его ответ):

SELECT 
    COUNT(
        IIF(jobs.status_id NOT IN (8,3,11), 1, 0)
    ) as active_count,
    COUNT(
        IIF(jobs.due_date < '2011-06-14' AND jobs.status_id NOT IN(8,11,5,3), 1, 0)
    ) as overdue_count,
    COUNT(
        IIF(jobs.due_date BETWEEN '2011-06-14' AND '2011-06-15 06:00:00.000000', 1, 0)
    ) as due_today_count
FROM 
    "jobs"
WHERE 
     jobs.creator_id = 5