sql - 使 PostgreSQL 查询更干

Question

我有以下 sql 查询:

SELECT
  date(survey_results.created_at),
  json_build_object(
    'high', COUNT(*) FILTER (WHERE (scores#>>'{medic,categories,food_insecurity}' in('high'))),
    'medium', COUNT(*) FILTER (WHERE (scores#>>'{medic,categories,food_insecurity}' in('medium'))),
    'low', COUNT(*) FILTER (WHERE (scores#>>'{medic,categories,food_insecurity}' in('low')))
  ) as food_insecurity,

  json_build_object(
    'high', COUNT(*) FILTER (WHERE (scores#>>'{medic,categories,motivation}' in('high'))),
    'medium', COUNT(*) FILTER (WHERE (scores#>>'{medic,categories,motivation}' in('medium'))),
    'low', COUNT(*) FILTER (WHERE (scores#>>'{medic,categories,motivation}' in('low')))
  ) as motivation                               
  FROM survey_results
  GROUP BY date(survey_results.created_at);

查询效果很好，但有很多重复。我在想怎样才能让它更干？这是您可以用来进行实验的 sql fiddle :

http://sqlfiddle.com/#!17/746c9/5

Answer 1

提高可读性的一种方法是提取scores#>>'{medic,categories,food_insecurity}:

SELECT
  date(survey_results.created_at),
  json_build_object(
    'high', COUNT(*) FILTER (WHERE (sub.food in('high'))),
    'medium', COUNT(*) FILTER (WHERE (sub.food in('medium'))),
    'low', COUNT(*) FILTER (WHERE (sub.food in('low')))
  ) as food_insecurity,
  json_build_object(
    'high', COUNT(*) FILTER (WHERE (sub.motivation in('high'))),
    'medium', COUNT(*) FILTER (WHERE (sub.motivation in('medium'))),
    'low', COUNT(*) FILTER (WHERE (sub.motivation in('low')))
  ) as motivation                               
  FROM survey_results
 JOIN LATERAL (SELECT scores#>>'{medic,categories,motivation}',
                      scores#>>'{medic,categories,food_insecurity}'
              ) sub(motivation, food)   ON true
 GROUP BY date(survey_results.created_at);

Rextester Demo

另一个步骤可能是 tablefunc或某种旋转以避免声明 high`medium\low` 3 次。

编辑:

使用 json_object_agg 的版本:

WITH cte AS (
    SELECT *
    FROM survey_results
    JOIN LATERAL (SELECT scores#>>'{medic,categories,motivation}',
                         scores#>>'{medic,categories,food_insecurity}'
                 ) s1(motivation, food) ON true
)
SELECT  cte.created_at::DATE
       ,MIN(s3.motivation)::json AS motivation
       ,MIN(s3.food_insecurity)::json AS food_insecurity
FROM cte
LEFT JOIN LATERAL (SELECT JSON_OBJECT_AGG(lvl, cnt_motivation)::text
                         ,JSON_OBJECT_AGG(lvl, cnt_food)::text
                   FROM(SELECT sub2.lvl
                            ,COUNT(*) FILTER (WHERE (sub2.lvl = sub.motivation))
                            ,COUNT(*) FILTER (WHERE (sub2.lvl = sub.food))
                        FROM cte sub
                        CROSS JOIN (VALUES ('high'),
                                         ('medium'), ('low')) AS sub2(lvl)
                        GROUP BY sub2.lvl
                       ) s2(lvl,cnt_motivation, cnt_food)
                  ) s3(motivation,food_insecurity)
          ON true
GROUP BY cte.created_at::DATE;

Rextester Demo 2