咸鱼溜溜六 发表于 2023-9-14 10:48:03

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

我得求助ORM不足的原始SQL(使用Django
1.7)。问题是大部分查询最终都有80个。-90%相似性。在不违反可重用性的靠的方法来构建查询,而不违反可重用性。
字符串连接是唯一的出路,即使用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 我怎样才能:
a)可选添加到上面WHERE约束,people 或
b)更改INNER JOIN为LEFT OUTER 或
c)更改COUNT为SUM 或
d)完全跳过该OVER / PARTITION条款?
                                                               
    解决方案:                                                               
                                                                更好的查询对于初学者,你可以修复语法,简化和澄清很多:
SELECT *FROM(SELECT p.person_id,p.name,p.team,sum(s.score)::int AS scorerank() OVER (PARTITION BY p.team                     ORDER BY sum(s.score) DESC)::int AS rnk    FROMperson p    JOINscores USING (person_id)    GROUP BY 1   ) subWHERErnk 基于我更新的表格布局。见下面的小提琴。不需要其他子查询。窗口函数是 在    集合函数 之后    执行,可以像演示一样嵌套。
当谈到等级时,你可能想使用它rank()而不是row_number()。
假设people.people_id是PK,则可以简化GROUP BY。
确保限制所有可能不清楚的列表
</u>PL / pgSQL函数然后,我会写一个plpgsql该函数接受可变部分的参数。a-c您的观点。d不清楚,请留待您补充。
CREATE OR REPLACE FUNCTION f_demo(_agg text       DEFAULT 'sum         _left_join boolDEFAULT FALSE         _where_name text DEFAULT NULL)RETURNS TABLE(person_id int,name text,team text,score int,rnk int) AS$func$DECLARE   _agg_opCONSTANT 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 scorerank() OVER (PARTITION BY p.team                     ORDER BY %1$s(s.score) DESC)::int AS rnk    FROMperson p    %2$sscores USING (person_id)    %3$s    GROUP BY 1   ) subWHERErnk'' 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在答案中几乎每一个细节都个细节。
页: [1]
查看完整版本: 通过编程构建强大的方法SQL查询