我想我在这里遇到了SQL查询的限制。我的表模式如下所示:
create table sessions (sessionId integer, browser text, ...);
-- primary key: sessionId
create table pageviews (sessionId integer, pageViewIndex integer, path text, ...);
-- primary key: sessionId, pageViewIndex
create table properties (sessionId integer, pageViewIndex integer, name text, value text);
-- primary key: sessionId, pageViewIndex, name
相同的列是相关的。这是一个非常简单的没有外键的数据库草稿。它是用SQLite创建的,并且只设计用于SQLite。
会话包含一个或多个关联的pageView。pageView可以包含零个或多个属性,每个属性具有不同的名称及其设置值。
我想找到所有会话的所有不同浏览器,并在其pageViews上为它们分配某个已分配属性的平均值,并按该值进行排序。我知道只有当属性的值是数字时才能工作。查询的用户必须关心这一点。
样本数据:
会话:
会话ID | 浏览器 |
---|---|
1 | 火狐 |
二 | 火狐 |
三 | 勇敢 |
四 | 火狐 |
浏览量:
会话ID | pageViewIndex | 路径 |
---|---|---|
1 | 1 | xyz |
1 | 二 | xyz |
二 | 1 | xyz |
三 | 1 | xyz |
四 | 1 | xyz |
(sessionId 1有两个页面浏览量,其他的只有一个;路径在这里无关紧要)
属性:
会话ID | pageViewIndex | 名称 | 价值 |
---|---|---|---|
1 | 二 | a | 10 |
二 | 1 | a | 二十 |
三 | 1 | a | 八十 |
(sessionId 1、pageViewIndex 1和sessionId 4没有设置属性,因此此统计查询不考虑它们。)
预期的查询结果如下:
| 浏览器|a_avg|
| --------------|--------------|
| 勇敢|八十|
| 火狐|十五岁|
这是因为有2个与firefox的会话(ID 1和2)具有属性“a”值为10和20的pageView,有1个与brave的会话(ID 3)具有属性“a”值为80的pageView。
我的查询也需要允许按任何列进行过滤,所以它应该尽可能小,而不会有连接等花哨的副作用。事实上,主查询连接是固定的,不能更改。例如,所考虑的数据可能会在sessions表或pageviews表中的其他列上进行过滤,或者甚至关联的属性(通过子查询)。过滤部分已经可以完美地独立工作,它不应该被这个查询破坏。
我目前的努力是这样的:
select s.browser,
(select avg(value)
from properties pr
where pr.sessionId = p.sessionId and pr.pageViewIndex = p.pageViewIndex and pr.name = 'a') a_avg,
from sessions s
join pageviews p on (p.sessionId = s.sessionId)
group by s.browser
order by a_avg desc
但是这会返回错误的值。我尝试了group_concat
来检查这里的平均值,而不是avg
。但是这里没有显示所有相关的值,而是只显示了一个值。所以这个查询中一定有什么严重的问题。我本来想尝试这个,但是聚合函数中显然不允许子查询:
select s.browser,
avg(select value
from properties pr
where pr.sessionId = p.sessionId and pr.pageViewIndex = p.pageViewIndex and pr.name = 'a') a_avg,
from sessions s
join pageviews p on (p.sessionId = s.sessionId)
group by s.browser
order by a_avg desc
有没有希望在SQL中解决这个问题,而不需要在应用程序内存中获取所有原始记录并进行统计?
1条答案
按热度按时间4smxwvx51#
使用所有3个表的简单连接和聚合,而不是相关子查询:
参见demo。