通过编程构建强大的方法SQL查询

咸鱼溜溜六

我得求助ORM不足的原始SQL（使用Django
1 r7 N$ Y7 p* d/ g( \1.7)。问题是大部分查询最终都有80个。-90％相似性。在不违反可重用性的靠的方法来构建查询，而不违反可重用性。
/ F& y6 r! K7 C$ j; }. l5 t7 k字符串连接是唯一的出路，即使用if-else构建无参数查询字符串，然后使用准备好的句子安全地包含参数(以避免SQL注入)。我想用一种简单的方法为我的项目模板SQL，而不是重新发明一个小的ORM。
例如，考虑以下查询：
SELECT id,name,team,rank_scoreFROM  ( SELECT id,name,team    ROW_NUMBER() OVER (PARTITION BY team                       ORDER BY count_score DESC) AS rank_score    FROM       (SELECT id,name,team       COUNT(score) AS count_score       FROM people       INNER JOIN scores on (scores.people_id = people.id)       GROUP BY id,name,team      ) AS count_table  ) AS rank_tableWHERE rank_score 我怎样才能：
5 ~( U7 \! ^$ }+ p( A" P6 }a）可选添加到上面WHERE约束，people 或
b）更改INNER JOIN为LEFT OUTER 或
c）更改COUNT为SUM 或
d）完全跳过该OVER / PARTITION条款？
4 N& `* n% V3 c+ u1 S; o9 s) U                                                               
    解决方案:                                                               
                                                                更好的查询对于初学者，你可以修复语法，简化和澄清很多：
SELECT *FROM  (  SELECT p.person_id,p.name,p.team,sum(s.score)::int AS score  rank() OVER (PARTITION BY p.team                       ORDER BY sum(s.score) DESC)::int AS rnk    FROM  person p    JOIN  score  s USING (person_id)    GROUP BY 1   ) subWHERE  rnk 基于我更新的表格布局。见下面的小提琴。不需要其他子查询。窗口函数是 在    集合函数 之后    执行，可以像演示一样嵌套。
6 i5 j" a) z9 Y2 z6 k( k当谈到等级时，你可能想使用它rank()而不是row_number()。
, s+ D1 w- o9 W/ ?: A假设people.people_id是PK，则可以简化GROUP BY。
确保限制所有可能不清楚的列表
' x. X1 e) _: b: q; E6 U</u>L / pgSQL函数然后，我会写一个plpgsql该函数接受可变部分的参数。a-c您的观点。d不清楚，请留待您补充。
CREATE OR REPLACE FUNCTION f_demo(_agg text       DEFAULT 'sum         _left_join bool  DEFAULT FALSE           _where_name text DEFAULT NULL)  RETURNS TABLE(person_id int,name text,team text,score int,rnk int) AS$func$DECLARE   _agg_op  CONSTANT text[] := '{count,sum,avg}';  -- allowed functions   _sql     text;BEGIN-- assert --IF _agg ILIKE ANY (_agg_op) THEN   -- all goodELSE   RAISE EXCEPTION '_agg must be one of %',_agg_op;END IF;-- query --_sql := format('SELECT *FROM  (  SELECT p.person_id,p.name,p.team,%1$s(s.score)::int AS score  rank() OVER (PARTITION BY p.team                       ORDER BY %1$s(s.score) DESC)::int AS rnk    FROM  person p    %2$s  score  s USING (person_id)    %3$s    GROUP BY 1   ) subWHERE  rnk  '' THEN 'WHERE p.name LIKE $1' ELSE '' END);-- debug   -- quote when tested ok-- RAISE NOTICE '%',_sql;-- execute -- unquote when tested okRETURN QUERY EXECUTE _sqlUSING  _where_name;   -- $1END$func$  LANGUAGE plpgsql;称呼：
SELECT * FROM f_demo();SELECT * FROM f_demo('sum',TRUE,      SELECT * FROM f_demo('avg',FALSE);SELECT * FROM f_demo(_where_name := '%1_'); -- named paramSQL小提琴
您需要对PL / pgSQL有深入的了解。否则，需要解释的地方太多了。plpgsql下的SO在答案中几乎每一个细节都个细节。