sql - 合并时间戳中按毫秒分组的多行

Question

这是我之前提出的问题的变体。我目前正在构建一个查询以从我的数据库中检索一些数据，我需要在一行中访问一些具有公共 id 的信息。

使用此查询：

select 
        missions_answer.response_id as "response",
        missions_answer.created as date,
        crm_player."document" as "document",
        missions_question.label as "label",
        missions_answertext.body as "bill #",
        missions_answerselectmultiple.body as "product",
        missions_answerinteger.body as "answer" 
from missions_answer 
    where  missions_answer.response_id = '71788176'
    group by missions_answer.response_id, crm_player.document,missions_answertext.body,
        missions_question.label,
        missions_answerselectmultiple.body ,
        missions_answerinteger.body,
        crm_user.first_name,
        crm_user.last_name
        order by date

这是我目前拥有的：

+   response    +          date        +   document    +    label    +    bill #  +    product  +  answer
-   71788176    -2020-07-29 19:05:41:00-     79907201    -    bill #   -    26899   -             -
-   71788176    -2020-07-29 19:05:41:01-     79907201    -    amount   -            -             -    1
-   71788176    -2020-07-29 19:05:41:02-     79907201    -    product  -      -    Shoes 1   -
-   71788176    -2020-07-29 19:05:41:03-     79907201    -    price    -            -             -  25.99
-   71788176    -2020-07-29 19:05:41:04-     79907201    -    amount   -            -             -    10
-   71788176    -2020-07-29 19:05:41:05-     79907201    -    product  -      -    Shoes 2   -
-   71788176    -2020-07-29 19:05:41:06-     79907201    -    price    -            -             -  32.00

注意顺序：amount然后是每个产品，由字段中的毫秒product name数给出。pricedate

filter在我的查询中使用：

select 
        missions_answer.response_id as Response,
        crm_player."document" as document,        
        MAX(missions_answertext.body) FILTER (WHERE missions_question.label = 'bill #') as "bill #",
        MAX(missions_answerselectmultiple.body::json ->> 'name') FILTER (WHERE missions_question.label = 'product') as product,
        MAX(missions_answerinteger.body) FILTER (WHERE missions_question.label = 'amount') as amount,
        MAX(missions_answerinteger.body) FILTER (WHERE missions_question.label = 'price') as "price"
from missions_answer 
    group by Response, product,amount, price
    

我得到这个：

+   response    +     document    +    bill #  +    product  +  amount  +   price 
-   71788176    -     79907201    -    26899   -     shoes 2   -       10  -   32.99 

这是一种随机组合值，但我正在寻找这个：

+   response    +     document    +    bill #  +    product  +  amount  +   price 
-   71788176    -     79907201    -    26899   -     shoes 1 -       1  -   25.99 
-   71788176    -     79907201    -    26899   -     shoes 2 -      10  -   32.00 

有没有办法根据时间戳给出的顺序在不同的行amount中获取每个产品？我仍然无法找到方法，在此先感谢您的任何提示或帮助。pricedate

标签： sqlpostgresql

Answer 1

我在此查询中看到的问题：

select 
        missions_answer.response_id as Response,
        crm_player."document" as document,        
        MAX(missions_answertext.body) FILTER (WHERE missions_question.label = 'bill #') as "bill #",
        MAX(missions_answerselectmultiple.body::json ->> 'name') FILTER (WHERE missions_question.label = 'product') as product,
        MAX(missions_answerinteger.body) FILTER (WHERE missions_question.label = 'amount') as amount,
        MAX(missions_answerinteger.body) FILTER (WHERE missions_question.label = 'price') as "price"
from missions_answer 
    group by Response, product,amount, price

是您按“金额”和“价格”分组，它们是通过聚合计算的值，我不确定您是否可以这样做，但我确信这不是一个高性能查询。

我建议拆分问题，使其更高效，也更清晰。

使用运算符 WITH ( https://www.postgresql.org/docs/9.1/queries-with.html ) 或子查询来执行此操作。

另外我建议检查数据结构，使用时间戳来匹配产品和价格至少很糟糕。无论如何，您可以尝试解决它：

WITH bills AS (
  SELECT response, bill_number
  FROM mission_answer
  WHERE missions_question.label = 'bill #'
)
, products AS (
  SELECT t1.response,
  t1.missions_question.label  as product,
  t2.price
  FROM mission_answer t1
  LEFT JOIN ( 
    SELECT temp.response, temp.date, temp.missions_answerinteger.body as price
    FROM mission_answer temp
    WHERE missions_question.label = 'price'
  ) t2
  ON t1.response = t2.response 
  AND t1.date = t2.date - '1 mili second'    --I am not sure about this sintax
  WHERE missions_question.label = 'product'
)

SELECT ...
FROM bills 
JOIN products