Как я могу агрегировать и сворачивать строки в таблице базы данных с помощью SQL?

У меня есть таблица с примерами данных, как показано ниже.

word       | last_seen  | first_seen | count
-----------|------------|------------|------
definition | 2014-09-08 | 2012-01-02 | 15
definition | 2014-10-11 | 2013-05-12 | 35
attribute  | 2013-07-23 | 2010-06-29 | 22

Я хочу агрегировать данные на месте, надеюсь, просто используя SQL, где данные для повторяющихся слов таковы, что я получаю MAX(last_seen), MIN(first_seen) и SUM(count).

word       | last_seen  | first_seen | count
-----------|------------|------------|------
definition | 2014-10-11 | 2012-01-02 | 50
attribute  | 2013-07-23 | 2010-06-29 | 22

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

SELECT 
  word, 
  MAX(last_seen) AS last_seen, 
  MIN(first_seen) AS first_seen, 
  SUM(count) AS count 
FROM 
  words 
GROUP BY word;

Однако я не просто хочу увидеть результирующую агрегацию... Я хочу фактически обновить таблицу words, заменив строки, содержащие повторяющиеся записи столбца word, агрегированными данными.


postgresql sql common-table-expression aggregate-functions duplicate-removal
person Bryan    schedule 10.12.2014    source источник
comment
Я не понимаю, почему ваш код не дает вам того, что вы хотите. можешь уточнить I just don't know how to update the words table in-place with the results   -  person Matt    schedule 10.12.2014
comment
У вас есть как минимум два оператора: один для обновления, другой для удаления уже неактуальной записи.   -  person crthompson    schedule 10.12.2014
comment
Вы уверены, что хотите обновить существующую таблицу, или вам просто нужно представление с агрегированными данными? А если снова изменится?   -  person sgeddes    schedule 10.12.2014
comment
Вы можете выполнить комбинированный выбор, обновление и удаление в одном запросе, используя предложение with: postgresql.org/docs/9.3/static/sql-select.html   -  person Wolph    schedule 10.12.2014

Ответы (2)


arrow_upward
1
arrow_downward

Насколько мне известно, в Postgresql (или любой другой традиционной СУБД, о которой я могу думать) нет «Редактировать на месте». Вместо:

  1. Возьмите результаты вашего запроса и поместите их во временную таблицу: CREATE TEMP TABLE <temptable> AS <Your Query> WITH DATA
  2. Удалите все в таблице word: TRUNCATE word; ‹ — это самая страшная часть, поэтому убедитесь, что вы хорошо разбираетесь в своем запросе, прежде чем усекать его.
  3. Вставьте записи из временной таблицы в теперь пустую таблицу word: INSERT INTO word SELECT * FROM <temptable>;
  4. Необязательно: удалите свою временную таблицу DROP TABLE <temptable>; (будучи временной таблицей, она будет удалена автоматически, когда вы завершите сеанс, но я предпочитаю быть явным)
person JNevill    schedule 10.12.2014

arrow_upward
0
arrow_downward

На самом деле вы можете сделать это в одном выражении, используя CTE, изменяющие данные:

WITH del AS (
   DELETE FROM words w
   WHERE EXISTS (
      SELECT 1 
      FROM   words w1
      WHERE  w1.word = w.word
      AND    w1.ctid <> w.ctid
     )
   RETURNING *
   )
INSERT INTO words(word, last_seen, first_seen, count)
SELECT word, MAX(last_seen), MIN(first_seen), SUM(count)
FROM   del
GROUP  BY word;

Тоже должно быть довольно эффективно.

Скрипт SQL.

О ctid:

О CTE:

person Erwin Brandstetter    schedule 10.12.2014