peewee 可以嵌套 SELECT 查询，以便外部查询选择内部查询的聚合吗? [英] Can peewee nest SELECT queries such that the outer query selects on an aggregate of the inner query?

问题描述

我将 peewee2.1 与 python3.3 和 sqlite3.7 数据库一起使用.

我想执行某些 SELECT 查询，其中:

1. 我首先选择一些聚合(计数、总和)，按一些 id 列分组；然后
2. 然后我从 (1) 的结果中进行选择，对其聚合进行聚合.具体来说，我想计算 (1) 中具有每个聚合值的行数.

我的数据库有一个事件"表，每个事件有 1 个记录，还有一个门票"表，每个事件有 1..N 张门票.每个票务记录都包含事件的 id 作为外键.每张票还包含一个座位"列，用于指定购买的座位数量.(门票"实际上最好被认为是在活动中购买 1 个或多个席位的交易.)

以下是此类工作 SQLite 查询的两个示例，它们为我提供了所需的结果:

SELECT ev_tix, count(1) AS ev_tix_n FROM(SELECT count(1) AS ev_tix FROM ticket GROUP BY event_id)按 ev_tix 分组SELECT Seat_tot, count(1) AS Seat_tot_n FROM(SELECT sum(seats) AS Seat_tot FROM ticket GROUP BY event_id)GROUP BY Seat_tot

但是使用 Peewee，我不知道在指定外部查询时如何选择内部查询的聚合(计数或总和).我当然可以为该聚合指定别名，但似乎我无法在外部查询中使用该别名.

我知道 Peewee 有一种执行原始"SQL 查询的机制，并且我已经成功地使用了该解决方法.但我想了解是否/如何直接使用 Peewee 完成这些查询.

解决方案

我在 peewee-orm Google 群组中发布了同样的问题.Charles Leifer 迅速做出了回应，并给出了对 peewee 大师的答复和新的承诺.所以虽然我是在回答我自己的问题，但显然所有功劳都归功于他.

您可以在此处查看该主题:https://groups.google.com/forum/#!topic/peewee-orm/FSHhd9lZvUE

但这是我从查尔斯对我的帖子的回复中复制的重要部分:

<块引用>

我已经向 master 添加了一些提交，它们应该可以让您进行查询可能的(https://github.com/coleifer/peewee/commit/22ce07c43cbf3c7cf871326fc22177cc1e5f8345")./p>

以下是第一个示例的大致语法:

SELECT ev_tix, count(1) AS ev_tix_n FROM(SELECT count(1) AS ev_tix FROM ticket GROUP BY event_id)按 ev_tix 分组ev_tix = SQL('ev_tix') # 别名的名称.(票.select(ev_tix, fn.count(ev_tix).alias('ev_tix_n')).从_(Ticket.select(fn.count(Ticket.id).alias('ev_tix')).group_by(Ticket.event)).group_by(ev_tix))

这将产生以下 SQL:

SELECT ev_tix, count(ev_tix) AS ev_tix_n FROM (SELECT Count(t2."id")AS ev_tix FROM "ticket" AS t2 GROUP BY t2."event_id")按 ev_tix 分组

I'm using peewee2.1 with python3.3 and an sqlite3.7 database.

I want to perform certain SELECT queries in which:

1. I first select some aggregate (count, sum), grouping by some id column; then
2. I then select from the results of (1), aggregating over its aggregate. Specifically, I want to count the number of rows in (1) that have each aggregated value.

My database has an 'Event' table with 1 record per event, and a 'Ticket' table with 1..N tickets per event. Each ticket record contains the event's id as a foreign key. Each ticket also contains a 'seats' column that specifies the number of seats purchased. (A "ticket" is really best thought of as a purchase transaction for 1 or more seats at the event.)

Below are two examples of working SQLite queries of this sort that give me the desired results:

SELECT ev_tix, count(1) AS ev_tix_n FROM 
(SELECT count(1) AS ev_tix FROM ticket GROUP BY event_id)
GROUP BY ev_tix

SELECT seat_tot, count(1) AS seat_tot_n FROM
(SELECT sum(seats) AS seat_tot FROM ticket GROUP BY event_id)
GROUP BY seat_tot

But using Peewee, I don't know how to select on the inner query's aggregate (count or sum) when specifying the outer query. I can of course specify an alias for that aggregate, but it seems I can't use that alias in the outer query.

I know that Peewee has a mechanism for executing "raw" SQL queries, and I've used that workaround successfully. But I'd like to understand if / how these queries can be done using Peewee directly.

解决方案

I posted the same question on the peewee-orm Google group. Charles Leifer responded promptly with both an answer and new commits to the peewee master. So although I'm answering my own question, obviously all credit goes to him.

You can see that thread here: https://groups.google.com/forum/#!topic/peewee-orm/FSHhd9lZvUE

But here's the essential part, which I've copied from Charles' response to my post:

I've added a couple commits to master which should make your queries possible (https://github.com/coleifer/peewee/commit/22ce07c43cbf3c7cf871326fc22177cc1e5f8345).

Here is the syntax,roughly, for your first example:

SELECT ev_tix, count(1) AS ev_tix_n FROM 
(SELECT count(1) AS ev_tix FROM ticket GROUP BY event_id)
GROUP BY ev_tix
ev_tix = SQL('ev_tix')  # the name of the alias.
(Ticket
 .select(ev_tix, fn.count(ev_tix).alias('ev_tix_n'))
 .from_(
     Ticket.select(fn.count(Ticket.id).alias('ev_tix')).group_by(Ticket.event))
.group_by(ev_tix))

This yields the following SQL:

SELECT ev_tix, count(ev_tix) AS ev_tix_n FROM (SELECT Count(t2."id") 
AS ev_tix FROM "ticket" AS t2 GROUP BY t2."event_id")
GROUP BY ev_tix

这篇关于peewee 可以嵌套 SELECT 查询，以便外部查询选择内部查询的聚合吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！