问Postgres，按记录添加组值之和。
EN

Stack Overflow用户

提问于 2017-11-24 08:09:03

回答 1查看 880关注 0票数 1

试图编写一个查询，该查询将获取已连接表的列status的和

我要加入的两个表是mission和media。任务记录可以有0->n个媒体记录.

目前，我的怪人返回这样的结果。

例如：

id      job_id  date        lat             long                status
1893    HAM267  13:53:42    51.501671242483 -0.22951886283813   2
1893    HAM267  13:53:42    51.501577520364 -0.22880621040992   1
1893    HAM267  13:53:42    51.50137231105  -0.22878203293347   0
1893    HAM267  13:53:42    51.501506139391 -0.22880008494156   2

但是，我希望对所有相关媒体记录进行分组，并添加它们的status列值。

1893    HAM267  13:53:42    51.501671242483 -0.22951886283813   5

我试着对子查询执行和，然后按每一列进行分组，但是这并没有产生错误的预期效果：

21000错误:由用作表达式的子查询返回的多行

查询

SELECT
  mis.id                                                         AS "ID",
  mis.job_id                                                     AS "Job ID",
  to_char(mis.date, 'DD/MM/YYYY')                                AS "Date",
  med.location_latitude                                          AS "Lat",
  med.location_longitude                                         AS "Long",
  (select sum(med.status) from media where med.mission_id = mis.id)  AS "Status Aggregate"
FROM mission mis
  LEFT JOIN media med
    ON mis.id = med.mission_id
GROUP BY mis.id, med.location_longitude, med.location_latitude, med.mission_id, med.state, med.file_path;

subquery

postgresql

回答 1

Stack Overflow用户

回答已采纳

发布于 2017-11-24 10:22:13

由于您没有提供任何关于如何检索纬度和经度的信息，所以我假设每个查询分别使用last_value和max：

--Sample
CREATE TEMP TABLE mission 
    (id, job_id, date) AS
    VALUES 
        (1893, 'HAM267'::TEXT, now()),
        (1894, 'XYZ', now());

CREATE TEMP TABLE media 
    (id, mission_id, location_latitude, location_longitude, status) AS
    VALUES 
        (1, 1893, 51.501671242483, -0.22951886283813, 2),
        (2, 1893, 51.501577520364, -0.22880621040992, 1),
        (3, 1893, 51.50137231105, -0.22878203293347, 0),
        (4, 1893, 51.501506139391, -0.22880008494156, 2),
        (5, 1894, 51.501671242466, -0.22951886283812, 7);

--Using DISTINCT ON. 
--You should add ORDER BY clause in OVER to avoid unpredictable results
SELECT
    DISTINCT ON (mis.id, mis.job_id, mis.date)
    mis.id AS "ID",
    mis.job_id AS "Job ID",
    to_char(mis.date, 'DD/MM/YYYY') AS "Date",
    first_value(med.location_latitude) 
        OVER(PARTITION BY mis.id, mis.job_id, mis.date) AS "Lat", 
    first_value(med.location_longitude) 
        OVER(PARTITION BY mis.id, mis.job_id, mis.date) AS "Long",
    sum(med.status) 
        OVER(PARTITION BY mis.id, mis.job_id, mis.date) AS "Status Aggregate"                                       
FROM mission mis
  LEFT JOIN media med
    ON mis.id = med.mission_id
ORDER BY
    mis.id, mis.job_id, mis.date;

--Using GROUP 
SELECT
    mis.id AS "ID",
    mis.job_id AS "Job ID",
    to_char(mis.date, 'DD/MM/YYYY') AS "Date",
    max(med.location_latitude) AS "Lat",
    max(med.location_longitude) AS "Long",
    sum(med.status) AS "Status Aggregate"                                       
FROM mission mis
  LEFT JOIN media med
    ON mis.id = med.mission_id
GROUP BY
    mis.id, mis.job_id, mis.date
ORDER BY
    mis.id, mis.job_id, mis.date;