根據 created_at 列的記錄選擇數據

我有兩個表關鍵字，ProjectReports：

http://sqlfiddle.com/#!15/06ae3/1

正如您在該範例中看到的，一切正常，但我想從這兩個表中獲取更多數據，但我不知道如何。

1. 在position列中，我只想顯示最小值和一個值，而不是那個數組。前任：$$ 1, 2, 3 $$ 將是 1
2. 如您所見，表keyword中id 4沒有任何數據project_reports，在這種情況下，我想在列中添加並value 0在列position column中添加目前日期CREATED_AT。
3. 此外，我想顯示相同關鍵字的位置值，其中table 中的created_at列project_reports具有 value now() - interval '1 day'; now() - interval '1 week'; now() - interval '1 mon'，如果position那些日子沒有 add 0。

預期輸出：

關鍵詞：

ID: 1 name: test
ID: 2 name: test1
ID: 3 name: test2

項目報告：

ID: 1, keyword_id: 1, project_id: 1, position: 1, created_at: '2014-07-09'
ID: 2, keyword_id: 1, project_id: 1, position: 2, created_at: '2014-07-09'
ID: 3, keyword_id: 1, project_id: 1, position: 3, created_at: '2014-07-10'
ID: 5, keyword_id: 1, project_id: 1, position: 4, created_at: '2014-07-10'
ID: 6, keyword_id: 2, project_id: 1, position: 1, created_at: '2014-07-09'

查詢後

k_name: test, k_id: 1, p_id: 1, position 3, yesterday_position: 1, created_at: '2014-07-10'
k_name: test1, k_id: 2, p_id: 1, position 0, yesterday_position: 1, created_at: '2014-07-10'
k_name: test2, k_id: 3, p_id: 1, position 0, yesterday_position: 0, created_at: '2014-07-10'
k_name: test, k_id: 1, p_id: 1, position 1, yesterday_position: 0, created_at: '2014-07-09'
k_name: test1, k_id: 2, p_id: 1, position 1, yesterday_position: 0, created_at: '2014-07-09'
k_name: test2, k_id: 3, p_id: 1, position 0, yesterday_position: 0, created_at: '2014-07-09'

請注意我在 fiddle中測試的稍微修改的架構。

使用實際的主鍵和正確的列名而不是id.

此外，您似乎只使用日期進行操作。所以我建議將您的timestamp列轉換為date.

第 1 項和第 2 項

SELECT k.keyword_id
    , k.name
    , pr.project_id
    , COALESCE(min(pr.position), 0) AS pos
    , COALESCE(pr.created_at, now()::date) AS created_at
FROM   keyword             k 
LEFT   JOIN project_report pr USING (keyword_id)
GROUP  BY k.keyword_id, pr.project_id, pr.created_at
ORDER  BY keyword_id, created_at
;

• 在 Postgres 9.1 或更高版本中， pk 列覆蓋GROUP BY.
• 用於COALESCE替換可能的NULL值。

對第 3 項的猜測

WITH cte AS (
  SELECT k.keyword_id
       , k.name
       , pr.project_id
       , COALESCE(min(pr.position), 0) AS pos
       , COALESCE(pr.created_at, now()::date) AS created_at
  FROM   keyword   k 
  LEFT   JOIN project_report pr USING (keyword_id)
  GROUP  BY k.keyword_id, pr.project_id, pr.created_at
  )
, x AS (
  SELECT DISTINCT ON (keyword_id, project_id) *
  FROM   cte
  ORDER  BY keyword_id, project_id, created_at DESC
  )
SELECT x.*
    , COALESCE(y.pos, 0) AS yesterday_pos 
    , COALESCE(w.pos, 0) AS week_pos 
    , COALESCE(m.pos, 0) AS month_pos 
FROM   x
LEFT   JOIN  cte y ON y.keyword_id = x.keyword_id
                 AND y.project_id = x.project_id
                 AND y.created_at = x.created_at - interval '1 day'
LEFT   JOIN  cte w ON w.keyword_id = x.keyword_id
                 AND w.project_id = x.project_id
                 AND w.created_at = x.created_at - interval '1 week'
LEFT   JOIN  cte m ON m.keyword_id = x.keyword_id
                 AND m.project_id = x.project_id
                 AND m.created_at = x.created_at - interval '1 month'
;

解釋

1. 在 CTE中cte，每(k.keyword_id, pr.project_id).
2. 在 CTEx中選擇最近的一天(k.keyword_id, pr.project_id)。
3. 在外部查詢LEFT JOIN中，最近一天x到cte多次從同(k.keyword_id, pr.project_id)一天/週/月之前檢索過去的值。

SQL小提琴。

引用自：https://dba.stackexchange.com/questions/70037