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

咸鱼溜溜六

我得求助ORM不足的原始SQL（使用Django
+ T& I& W6 |$ f" H$ r, V1 |2 c  c1.7)。问题是大部分查询最终都有80个。-90％相似性。在不违反可重用性的靠的方法来构建查询，而不违反可重用性。
字符串连接是唯一的出路，即使用if-else构建无参数查询字符串，然后使用准备好的句子安全地包含参数(以避免SQL注入)。我想用一种简单的方法为我的项目模板SQL，而不是重新发明一个小的ORM。
例如，考虑以下查询：
4 f9 E* _, p% Y# A; ]9 D# Y2 Y/ JSELECT 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 我怎样才能：
a）可选添加到上面WHERE约束，people 或
b）更改INNER JOIN为LEFT OUTER 或
( b. s* z+ J! _c）更改COUNT为SUM 或
# [2 O* B5 ]/ ~d）完全跳过该OVER / PARTITION条款？
                                                               
/ k  S' X$ ]: z& q    解决方案:                                                               
                                                                更好的查询对于初学者，你可以修复语法，简化和澄清很多：
* t7 T  R) m( S; U! USELECT *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 基于我更新的表格布局。见下面的小提琴。不需要其他子查询。窗口函数是 在    集合函数 之后    执行，可以像演示一样嵌套。
当谈到等级时，你可能想使用它rank()而不是row_number()。
$ N. [  ^2 o/ Y$ }& @" z/ m假设people.people_id是PK，则可以简化GROUP BY。
确保限制所有可能不清楚的列表
* X7 b5 s- n' }& X5 ~% U! z</u>L / pgSQL函数然后，我会写一个plpgsql该函数接受可变部分的参数。a-c您的观点。d不清楚，请留待您补充。
) P' z; m) U' }+ k4 p$ j* n1 A" }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;称呼：
6 P- J$ }* p7 ]) {+ a: S/ w0 kSELECT * FROM f_demo();SELECT * FROM f_demo('sum',TRUE,      SELECT * FROM f_demo('avg',FALSE);SELECT * FROM f_demo(_where_name := '%1_'); -- named paramSQL小提琴
& @. i1 A9 Z& l, T, @/ L0 P您需要对PL / pgSQL有深入的了解。否则，需要解释的地方太多了。plpgsql下的SO在答案中几乎每一个细节都个细节。