sql - 我在一个大表上进行了许多相关但单独的查询。我可以以某种方式组合这些并重用一些子查询吗？

Question

我有一张大桌子，比如说大约 100 万行。在运行时，我需要在给定一些参数的情况下对该表进行分析。分析执行几个查询，一些相关，一些不相关。

鉴于表

create table "Participant" (
  "championId" integer NOT NULL,
  "keystone" integer NOT NULL,
  "mythic" integer,
)

我需要

1. 参加人数
2. ChampionId = n 的参与者人数
3. 按基石分组的参与者人数
4. 按神话分组的参与者人数
5. ...我将来会想提出更多类似于上述问题的查询

我没有太多的 SQL 经验，但我想以编程方式解决这个问题的方式是

1. 获取所有行并计算它们
2. 提取具有 ChampionId = n 的行的子集，将其称为 S，并计算它们
3. 通过 keystone 对子集 S 进行分组，并对每个值进行计数
4. 按神话对子集 S 进行分组，并计算每个值
5. ...

但是现在我实际上正在实现执行查询的服务器，看来我需要进行 4 个单独的查询：

select count(*) from "Participant";
select count(*) from "Participant" where "championId" = n;
select keystone, count(*) from "Participant" where "championId" = n group by keystone;
select mythic, count(*) from "Participant" where "championId" = n group by keystone;
...

这对我来说似乎很糟糕，因为

1. 4 次单独的数据库往返
2. 必须从头开始进行 4 个单独的查询。一些查询的结果应该能够被其他人重用。查询 3 < 查询 2 < 查询 1，并且查询 4 ​​< 查询 2。也就是说，查询 2 的行是查询 1 的行的子集。因此，必须再次遍历所有行对我来说似乎很糟糕。

如何改进我的工作流程？

标签： sqldatabasepostgresql

Answer 1

你可能想错了。谈到您的具体问题...

4 次单独的数据库往返

是的，这是真的，但理想情况下这些会同时发生。IE。在触发查询 2 之前，您不应该等待查询 1 返回。并行运行查询可以有效地使往返延迟保持不变。

您如何实现这一点取决于您的应用程序（发出这些查询的东西）内置的平台/语言。您需要使用一些异步/等待或线程工具。

必须从头开始进行 4 个单独的查询。一些查询的结果应该能够被其他人重用。查询 3 < 查询 2 < 查询 1，并且查询 4 ​​< 查询 2。也就是说，查询 2 的行是查询 1 的行的子集。因此，必须再次遍历所有行对我来说似乎很糟糕。

当您看到多个具有相似条件的查询时，很容易想到这一点。（有时是违反直觉的）事实是，数据库非常擅长针对索引良好的表解析查询，并且通常，查询越简单，查询规划器就越容易构建可以快速解决的查询。试图通过构建更复杂的查询来变得聪明有时会适得其反。

例如，将所有记录"championId" = 1拉出然后对其执行多个查询似乎是个好主意，但实际上这可能需要数据库将大量数据复制到内存中。另外，这个临时数据集的索引可能与原始表不同，这意味着后续查询虽然针对较小的数据集运行，但可能仍然较慢。

肯定有一些时候隔离一小组昂贵的数据然后以多种方式查询它是正确的解决方案，但您给出的示例不是其中之一。换句话说，你可能想多了。Postgres 是一个功能强大且成熟的平台，它的全部工作都是使用查询。命中索引列的简单比较通常会非常快。

（这完全取决于您的具体情况，因此如果有疑问，请对其进行基准测试。）

---

好的，但我仍然想要

即使可能没有必要，仍想将多个查询组合在一起？好吧，让我们试试吧。。

单个查询总是会返回单个记录集。也就是说，您总是会取回一组“矩形”数据，其中每一行的每一列都有一个值（即使它是null）。因此，您可以通过两种方式在查询中添加其他数据：添加更多行或添加更多列。

首先让我们设置一些测试数据：

-- Create table
create table "Participant" (
  "championId" integer NOT NULL,
  "keystone" integer NOT NULL,
  "mythic" integer
);

-- Create some random test data
insert into "Participant" ("championId", "keystone", "mythic")
select floor(random() * 10 + 1) as "championId", floor(random() * 10 + 1) as "keystone", floor(random() * 10 + 1) as "mythic"
from generate_series(1, 10000000);

添加行

为此，您将使用union运算符。union获取多个查询的结果并将它们首尾相连，但当然，它需要查询具有相同的列；相同的名称，相同的类型，相同的顺序。不过这很容易，我们可以用值填写我们不使用的列null，如下所示：

-- Select counts
select 
    'All participants' as label, 
    null::int as keystone, 
    null::int as mythic, 
    count(*) as count
from "Participant"
union

select
    'Participants with championId 1' as label,
    null::int as keystone,
    null::int as mythic,
    count(*) as count
from "Participant"
where "championId" = 1
union

select 'Grouped by keystone' as label,
    keystone,
    null::int as mythic,
    count(*) as count
from "Participant"
where "championId" = 1
group by keystone
union

select 'Grouped by mythic' as label,
    null::int as keystone,
    mythic,
    count(*) as count
from "Participant"
where "championId" = 1
group by mythic;

你会注意到我们基本上仍然只是在这里做多个查询，结果只是一起返回。您提到想要提取一组特定的数据并将其重用于多个后续查询；在这种情况下，您可以使用with运算符并执行以下操作：

-- Count all
select 
    'All participants' as label, 
    null::int as keystone, 
    null::int as mythic, 
    count(*) as count
from "Participant";

-- Count records for "championId" = 1
with s as (
    select *
    from "Participant"
    where "championId" = 1
)
select
    'Participants with championId 1' as label,
    null::int as keystone,
    null::int as mythic,
    count(*) as count
from s
union

select 'Grouped by keystone' as label,
    keystone,
    null::int as mythic,
    count(*) as count
from s
group by keystone
union

select 'Grouped by mythic' as label,
    null::int as keystone,
    mythic,
    count(*) as count
from s
group by mythic;

但是，正如我在顶部的注释中所提到的，这可能不会给您带来预期的性能提升。

添加列

实现与您想要的类似的另一种方法是对一个查询进行更多计数，如下所示：

select 
    count(*) as "all",
    count(case when "championId" = 1 then 1 else null end) as "cham1",
    count(case when "championId" = 1 and keystone = 1 then 1 else null end) as "cham1ks1",
    count(case when "championId" = 1 and keystone = 2 then 1 else null end) as "cham1ks2",
    count(case when "championId" = 1 and keystone = 3 then 1 else null end) as "cham1ks3"
    -- ..
from "Participant";

这只有在您提前确切知道您需要多少值时才有效。keystone在您当前的使用中，这是不正确的——您不知道分组的计数并将mythic带回多少计数。

如果您非常热衷，还可以采用其他一些更奇特的方法——比如数据透视表或将多个值折叠到一个 json 数组中——但是，正如上面所讨论的，即使这两种简单的方法也可能没有必要。