Postgres необходимо получить количество строк по уникальности

#sql #postgresql

Вопрос:

У меня есть простая таблица, которая имеет широту, длину и время. В принципе, я хочу, чтобы результат моего запроса выдавал мне что-то вроде этого:

lat,long,hourwindow,count

Кажется, я не могу понять, как это сделать. Я перепробовал так много вещей, что не могу их правильно использовать. И, к сожалению, вот что у меня пока есть:

 WITH all_lat_long_by_time AS (
    SELECT
      trunc(cast(lat AS NUMERIC), 4) AS lat,
      trunc(cast(long AS NUMERIC), 4) AS long,
      date_trunc('hour', time :: TIMESTAMP WITHOUT TIME ZONE) AS hourWindow

    FROM my_table
),
    unique_lat_long_by_time AS (
      SELECT DISTINCT * FROM all_lat_long_by_time
  ),
  all_with_counts AS (
   -- what do I do here?
  )
SELECT * FROM all_with_counts;

1. Пожалуйста, объясните, как именно определяется «количество строк по уникальности». Вы имеете в виду количество уникальных строк (после усечения чисел)? Итак, количество различных (lat, long) строк в час? Версия Postgres и определение таблицы также всегда полезны. time :: TIMESTAMP WITHOUT TIME ZONE выглядит подозрительно.

Ответ №1:

Я думаю, что это довольно простой запрос агрегации:

 SELECT date_trunc('hour', time :: TIMESTAMP WITHOUT TIME ZONE) AS hourWindow
       trunc(cast(lat AS NUMERIC), 4) AS lat,
       trunc(cast(long AS NUMERIC), 4) AS long,
       COUNT(*)
FROM my_table
GROUP BY hourWindow, trunc(cast(lat AS NUMERIC), 4), trunc(cast(long AS NUMERIC), 4)
ORDER BY hourWindow

Ответ №2:

Если «количество строк по уникальности» предназначено для подсчета различных координат в час (после усечения чисел), count(DISTINCT (lat,long)) выполняет свою работу:

 SELECT date_trunc('hour', time::timestamp) AS hour_window
     , count(DISTINCT (trunc( lat::numeric, 4)
                     , trunc(long::numeric, 4))) AS count_distinct_coordinates
FROM   tbl
GROUP  BY 1
ORDER  BY 1;

Подробности в руководстве здесь.
(lat,long) является значением строки и сокращением от ROW(lat,long) . Подробнее здесь.

Но count(DISTINCT ...) обычно выполняется медленно, в вашем случае подзапрос должен выполняться быстрее:

 SELECT hour_window, count(*) AS count_distinct_coordinates
FROM  (
   SELECT date_trunc('hour', time::timestamp) AS hour_window
        , trunc( lat::numeric, 4) AS lat
        , trunc(long::numeric, 4) AS long
   FROM   tbl
   GROUP  BY 1, 2, 3
   ) sub
GROUP  BY 1
ORDER  BY 1;

Или:

 SELECT hour_window, count(*) AS count_distinct_coordinates
FROM  (
   SELECT DISTINCT
          date_trunc('hour', time::timestamp) AS hour_window
        , trunc( lat::numeric, 4) AS lat
        , trunc(long::numeric, 4) AS long
   FROM   tbl
   ) sub
GROUP  BY 1
ORDER  BY 1;

После того, как подзапрос сворачивает дубликаты, внешний SELECT может использовать обычный count(*) запрос.