今天看到了这个图片，觉得挺有意思的，能够写一篇博文评论一下。

从一个SQL Count比方说开去

本文的部分创意和构思来自以下链接：

www.eversql.com/how-to-spee…

Count和聚合函数

这儿运用的Count函数意为“计数”。实践上，它是一个聚合函数，它用于对记载集依照字段的数值进行分组，然后核算每个分组中，记载的数量。这儿面，聚合的意思便是将记载集依照某个字段的数值进行分组，然后核算一些核算信息或许分组中的数组特征。计数，当然是其间的一个，除了计数之外，常用的聚合函数还包括：

  • 求和: Sum

关于聚合组中的记载字段的数值进行求和，只支撑数值类型的字段，或许需求转换为数值。

  • 求平均值: Avg

关于聚合组中记载的字段值，求平均值。

  • 求最大值或最小值: Max/Min

找出聚合组中数值的最大值和最小值。

这些函数的根本办法和操作办法都是相似的，咱们能够将这一类函数都称为聚合函数。在PG和规范SQL中，聚合函数运用Group By子句完成，它其实是Select句子的一个可选项，其规范办法是：

SELECT
   column_1, 
   column_2,
   ...,
   aggregate_function(column_3)
FROM 
   table_name
GROUP BY 
   column_1,
   column_2,
   ...;

这儿:

  • 根本办法是一个SELECT..FROM..GROUP BY句子
  • GROUP BY: 聚合操作关键字
  • 指明需求进行聚合核算运用的字段，能够运用多个字段参加分组，分组依据便是这些字段值的组合
  • 假如不运用GROUP BY，则在整个记载集中运用聚合操作，其实便是将整个记载集当作一组
  • SELECT内容能够也能够不包括参加聚合的字段，但不能包括不参加分组的字段，便是不能有其他字段

好了，了解了聚合函数的根本概念和办法后，咱们回到文章开头的主题，便是Count这个聚合函数的几种运用办法，和它们之间的差异。为了简化评论，文初的场景，没有用到分组办法，规模是整个记载集。它们都是运用Count函数，差异主要是其间的参数，有这么几种情况：

  • count(*)

这个能够用来计数一切满意条件的记载行的数量，无论它的值是多少。这个常常用到核算表中的记载数量，而不太关怀详细的记载字段中的数值。还有一种count(1)的写法，其实成果是相同的。因为尽管逻辑上，count* 会查看一切的列中的行，count1只查看记载存在，概念上仍是略有差异，但实践上SQL履行优化程序会忽略这一点。

  • count(name)

假如count的参数是某一个字段，那么它的躲藏的语义是核算这个字段中，非null的数值的记载的数量。

  • count(distinct name)

先对name字段进行去重字段，然后再查看非重复的称号的数量。

下面咱们经过一些详细的SQL示例，来详细感受和阐明一下这个函数的运用办法。

SQL示例

示例数据

在正式开始之前，咱们先来结构一些示例数据，咱们不用创立实在的数据表，能够经过以下办法(CTE)构建一个记载集：

with T( id,name, nation, dep ) as (values
(1, 'John', 'England', 1),
(2, 'Tom', 'France',1),
(3, 'Alexda', 'England',3),
(4, 'Smith', null, 3),
(5, 'Bob', 'USA', 4 ))
--select count(*), count(1), count(nation), count(distinct(nation)) from T;
select gp, count(*) star, count(1) one, count(nation) nation, count(distinct(nation)) nation2 from T group by 1;

当然，依照SQL履行的原理，咱们有必要要把这个代码，放在一个实在的SQL连接环境里边，才干运转，所以仍是需求一个实在的数据库，只不过能够不影响和操作那个数据库里边的数据罢了。

聚合查询完成

依据上面的数据，咱们能够运用下面的SQL句子，来完成前面几种Count聚合查询办法如下：

-- 记载集
select count(*), count(1), count(nation), count(distinct(nation)) nation2
from T;
star	one	nation	nation2
5	5	4	3
-- 分组
select gp, count(*) star, count(1) one, count(nation) nation, count(distinct(nation)) nation2 from T group by 1;
gp	star	one	nation	nation2
1	2	2	2	2
3	2	2	1	1
4	1	1	1	1

示例代码中能够看到，为了便利评论和比较，咱们将这几种Count办法，都放在了同一个句子傍边。彻底的验证了前面的表述和内容。

此外，Postgres的GroupBy子句，还支撑“逻辑字段”，便是运用字段序号，来代替字段的称号(这儿便是GP)，能够便利代码的编写和搬迁。

Count Distinct

除了Count的根本运用之外，文首引用的文章，其中心，其实是在评论一个Distinct功用和优化的问题。便是Count(distinct(name))办法，假如遇到比较大的数据规划，或许会有潜在的功用问题。就需求对这个操作进行优化，来进步操作速度。咱们将在下一章节深化评论。

Distinct优化

本章节，咱们运用原文供给的办法，详细的来评论怎么在比较大的数据集中，加快和优化核算核算的办法。

数据预备

为了更便利评论和比较，这儿仍是运用原文中的示例数据和办法，而且在一个实在的postgres数据库中，创立相关的数据库和示例数据，如下所示：


create table EXAMPLE_TBL (
    Id serial primary key,
    Username text,
    Purchase_Date Date,
    Product text,
    Qty int
);
INSERT INTO EXAMPLE_TBL (Username, Purchase_Date, Product, Qty)
SELECT 'User' || floor(random() * 200 + 1)::int, 
    CURRENT_DATE - floor(random() * 5 + 1)::int,  
    CASE floor(random() * 3 + 1)::int 
        WHEN 1 then ''
        WHEN 2 then ''
        WHEN 3 then ''
    END,
    floor(random() * 10 + 1)::int
FROM generate_series(1,1000000);

上述代码会在数据库创立一个示例数据表，并随机填充1000000条数据记载。

原生操作和根本进程

数据预备好之后，咱们下面将施行原生的count-distinct操作：

select count(distinct(username) ) from example_tbl;

在笔者的体系中，此规范查询大约耗时1987ms。

从原理上而言，与经典计数比较，distinct去重并核算的操作通常较慢。因为它需求履行两个操作：

  • 它需求扫描整个记载集，列出不重复的字段值，这个操作自身就比较杂乱和低效
  • 对处理后的字段值进行计数

为了改进这一操作的功率，在原文中，提出了下列能够考虑的加快和优化计划。

数据库预算

这是一个典型的工程化处理办法。关于很大的数据，在事务上，特别是核算意义上，其实并不需求一个完整准确的数字。比方一个百万级的用户分类核算，1568121和1568120，在事务需求上根本上是没有差异的。假如能够接纳这一点，咱们能够运用数据库的预算功用，而非彻底准确的核算。

许多联系型数据库体系，比方postgres在一些体系的根底信息表中，现已包括了一些近似基数的核算数据，能够用来进行预算，而不需求实在的去扫描实在的数据库表。

相关的操作如下：

// 剖析数据表，更新体系剖析和预算数据
ANALYZE EXAMPLE_TBL;
// 查询预算数据
SELECT reltuples AS estimate FROM pg_class WHERE relname = 'example_tbl';
// 创立预算解说计数程序
CREATE OR REPLACE FUNCTION count_estimate(
    query text
) RETURNS integer LANGUAGE plpgsql AS $$
DECLARE
    plan jsonb;
BEGIN
    EXECUTE 'EXPLAIN (FORMAT JSON)' || query INTO plan;
    RETURN plan->0->'Plan'->'Plan Rows';
END;
$$;
// 运转预算解说计数程序
SELECT count_estimate('SELECT DISTINCT username FROM example_tbl');

在笔者的体系中，此操作耗时372ms，但成果的确不是准确的(实践应该是203，得到200)。但耗时约为原始查询的四分之一。

简要阐明如下：

  • pg_class作为体系表，保存了许多当时数据表的核算信息
  • 运转analyze能够对数据库表进行剖析，并更新这些核算信息
  • 能够运用relname，来过滤和查找特点的数据库目标，这儿是示例数据表
  • 剖析之后，在履行查找，能够得到数据库表行的数量
  • 结合预算信息，结合计划任务，能够快速的估量查询句子要触及到的行记载的数量
  • 预算计数操作，在本例中被封装为一个字界说函数
  • 运用数据库预算，能够快速取得一个大致而非准确的成果

物化视图

假如的确需求准确的核算成果，但又想要一个比较高的查询功用，能够考虑运用物化视图，作为一种缓存机制。

// 创立物化视图
CREATE MATERIALIZED VIEW EXAMPLE_MW AS 
SELECT PRODUCT, COUNT(DISTINCT USERNAME) 
FROM EXAMPLE_TBL GROUP BY PRODUCT;
// 改写物化视图
REFRESH MATERIALIZED VIEW example_mw;
// 查询物化视图
SELECT * FROM example_mw;

在笔者的体系中，原生查询需求3970ms，运用物化视图查询需求359ms，更新物化视图需求3200ms。

物化视图便是一种持久化的视图，它常常用于将查询成果保存在数据库中，并运用简略而相对高效的查询成果数据更新机制。本质上，它便是一种数据缓存机制，查询时，能够直接从缓存的记载表中回来成果，到达进步查询功用的意图。它并没有改动原始查询的功用，但是它是准确的，在某些场景下是能够重复运用的，代价是耗费一些存储空间。运用物化视图需求注意视图更新的机遇，所以它比较适合于写少读多的运用场景。

专用数据结构聚合: HyperLogLog(HLL)

HyperLogLog(HLL，无正式译名，笔者译为混合对数日志)是一种概率性的数据结构，用于估量一个调集中不同元素的基数(cardinality)，即调集中不同元素的数量。HLL依据哈希函数和位运算来进行估量，经过将输入元素哈希成固定长度的二进制串，然后运用位运算进行核算。在进行预算时，HLL会运用一些特定的技巧来处理哈希值，以到达减小估量差错的意图。比较于准确计数办法，HLL能在牺牲必定的准确性的情况下，显著削减内存运用。实践上，HLL的内存占用是固定的，与处理的元素数量无关。而且， HLL的核算是能够并行进行的，能够别离核算多个部分，然后将成果兼并。而且，相对而言，HLL对更大规划大数据集的基数估量效果相对更好，能够供给相对准确的成果。这些特性都使得它适用于大规划的数据调集。

HLL不是Postres原生的功用，需求经过扩展模块来运用。下面的示例能够让咱们对它的运用办法有一个初步了解：

// 创立扩展模块
CREATE EXTENSION hll;
// 核算专用数据表
CREATE TABLE daily_users (
    Purchase_Date date UNIQUE,
    users hll
);
// 核算数据录入和更新 
INSERT INTO daily_users
SELECT Purchase_Date, hll_add_agg(hll_hash_text(Username))
FROM EXAMPLE_TBL GROUP BY 1;
// 基数查询
SELECT Purchase_Date, hll_cardinality(users) 
FROM daily_users;
// 
SELECT hll_cardinality(hll_union_agg(users))
FROM daily_users WHERE Purchase_Date >= CURRENT_DATE -2;

在PG中，HLL字段是二进制编码的，无法直接查询和运用，需求合作相关函数东西。所以HLL的运用有必定的学习和运用门槛，需求熟悉和了解相关的概念和办法。要满意不同的核算查询需求，或许需求组合运用不同的HLL办法。关于这些内容，因为笔者的了解有限还有篇幅的约束，本文中就不做深化评论了。有爱好的读者能够仔细阅览原文，获取更多的信息。

作为一个功用的比较，在笔者的测试环境中，HLL查询需求2295ms；插入需求584ms；查询基数643ms；

运用掩盖索引 Covering Index

前面说到的物化视图和HLL的优化计划，都有一个问题是无法供给实时的成果，而且要尽量挨近实在状况，需求阶段性的数据改写操作。为此，这儿有一个代替计划： 掩盖索引(Convering Index)。

掩盖索引是一种优化数据库查询功用的技能。当一个查询能够彻底经过索引满意时，即不需求去查找表中的实践数据行，就称之为掩盖索引。这种索引包括了查询所需的一切列，因而能够直接供给查询的成果，而无需额外地拜访表。明显，这种工作办法能够显著削减查询的IO操作，数据库引擎能够直接从索引中获取所需的数据，而不必去查找底层的实在数据表。这关于大型数据库和频频履行读取操作的场景非常有用，能够提升查询功用。

下面的示例，能够协助咱们了解这一技能：

// 根本查询
SELECT PURCHASE_DATE, COUNT(DISTINCT USERNAME)
FROM EXAMPLE_TBL GROUP BY 1;
// 界说掩盖索引
CREATE INDEX EXAMPLE_COVERING_IDX ON EXAMPLE_TBL(Purchase_date) INCLUDE(Username);
// 解说查询计划
EXPLAIN select Purchase_date, count(distinct Username) from EXAMPLE_TBL group by Purchase_date;
GroupAggregate  (cost=0.42..23853.58 rows=5 width=12)
  Group Key: purchase_date
  ->  Index Only Scan using example_covering_idx on example_tbl  (cost=0.42..18853.50 rows=1000005 width=11)

在笔者的测试体系中，根本查询耗时7586ms；创立索引5978ms; 解说计划311ms。能够看到掩盖索引创立后，只运用索引来履行查询。优化后的根本查询耗时4507ms，有改进，但如同不是很大，优点是实时又准确。

这个技能还有一些约束。添加索引或许会减慢表上的任何写入速度，因为它还需求更新索引。而且，索引是数据库中的物理数据结构，它们占用空间，而且需求定时保护(数据库主动保护)以保持其功用。

查询剖析和优化产品

PG内置了Explain查询计划解说句子和东西，能够奉告当时查询言语的工作办法。其实关于一般查询类操作的优化，只要一个主要和简略的原则：“尽或许多的运用索引并防止进行全表扫描”。查询剖析东西能够协助剖析查询履行计划、触及的步骤和记载调集、是否运用索引等相关信息，协助开发者来编写正确的查询言语，树立正确适宜的索引，来进步查询功用。

对查询计划解说句子的成果进行解读和剖析对专业性有相当高的要求，运用起来并不是非常简略便利。所以商场也有一些产品化的软件，如各种查询优化器等等，能够供给更详细和直观的信息和主张。比方直接奉告，针对当时的查询操作，应当在哪些字段上树立索引等等。开发者也能够合作查询计划解说程序运用，来评价优化计划。

优化数据库结构

假如仔细思考前面示例数据的触及，结合详细的事务需求，就会发现，针关于这个核算事务需求，这样的相似于Excel表格，将一切数据和模型都放在一起的数据库结构的规划并不是特别合理。

正确的办法是，能够考虑规划多个数据表，来承载不同的数据模型，每个数据表中的数据记载(模型)是能够用数据标识来到达唯一性；然后运用数据关联联系(一对多、多对多)的将数据记载(模型)关联起来。这样，其实就不存在需求去重的操作了。

从这儿也能够看到，许多的disctinct问题，其实也不是真的重复记载的问题了。

约束记载集

在某些运用场景和数据集傍边，或许不需求处理整个记载集，而只需求处理一部分数据和记载，就能够得到令人能够承受的核算成果。而合理的依据实践情况，选取部分数据集来进行处理，能够大幅度下降输入处理的数量，然后削减查询处理所需求的时间。

下面是一个简略的比方：

SELECT COUNT(DISTINCT Username)
FROM (SELECT Username from EXAMPLE_TBL ORDER BY ID LIMIT 10000) SUB;

这儿假定咱们尽管有100万条数据记载，但当时的事务需求是核算不同名的用户数量，就能够运用这个“妥协”的技能计划来处理，并到达一个能够承受的成果。Postgres供给了limit指令，来约束处理数据集的规划，能够便利的运用来结构查询剖析子记载集。

这种办法，或许能够适用于以下一些情形：

  • 数据均匀散布

理想情况下，一个一般的用户事务体系，用户在事务中的散布是比较均匀的，特别是在靠前的事务数据傍边。这样，其实咱们能够只核算前一万条记载，就能够得到一个比较挨近实践的情况。或许，依据用户体验的要求，仅仅需求供给一个有限的不重复的数据调集，也能够考虑这个技能计划。

  • 用户感知

许多时分，特别是关于终究的人类用户，向其展现很多准确的信息是不必要的，它超出了大多少用户的感知和信息处理能力的规模，比方，假如用户正在处理显现前50个成果的列表，那么向终究用户供给500个成果并不能实在供给太多附加价值。

  • 事务约束

同样的道理，在许多事务场景中，仅仅需求供给一个不包括重复记载的列表（比方一个可用的名单），也能够运用这些技能计划。

从头评价事务需求

上面的评论，供给了许多技能计划和选项。但假如咱们回到问题的源头。就会发现更深层次的问题是是否有必要要用这种办法来满意事务需求，或许说，这个需求是否只能运用这种办法。在许多时分，或许需求运用不同的办法，才干找到更好更适宜的处理办法。

依据原文的启示，笔者也将前面的评论内容进行了总结、梳理和笼统，并绘制了相关的思维和决策流程框图，来协助剖析实在的事务需求和处理计划，以及挑选适宜的技能处理计划。

从一个SQL Count比方说开去

扩展信息

依据根本聚合函数，在实践运用中，或许有一些扩展的运用场景，这儿简略的总结一下。限于篇幅，这儿仅仅概念性的阐明，不会深化评论。

  • 查询条件: where

在实践运用场景中，通常不会直接运用整个记载表和一切的记载，而是常常需求先对记载集进行过滤，挑选契合条件的记载，然后再来进行聚合核算。这时能够运用Where子句，和Select句子中的办法是相同的。

  • 聚合查询条件: having

一般的Where子句，用于结构契合条件的记载集。但咱们还会遇到一种情况，便是需求对聚合的成果进行过滤，便是需求将聚合成果作为数据过滤的条件。这时就不能再运用规范Where条件查询，而是需求运用Having子句。下面是一个简略的用例：

SELECT city, count(*), max(temp_lo) FROM weather GROUP BY city
HAVING max(temp_lo) < 40;
  • 字段值过滤器： filter

在Postgres中，聚合函数，能够和字段值过滤器filter结合运用，结构更灵活更强壮的查询操作。

SELECT city, count(*) FILTER (WHERE temp_lo < 45), max(temp_lo)
FROM weather GROUP BY city;

能够看到，filter能够针对每个字段都设置预过滤条件，然后能够在一个句子中，表达更杂乱的查询操作，或许在同一个查询成果中，取得不同条件组合的聚合核算核算成果。

  • 窗口函数

从上面的剖析和评论，咱们也能够发现，聚合函数，尽管能够处理一些分组核算剖析的问题，但它只能将。这时分，就需求引入窗口函数。

当然，窗口函数的操作，也会带来更大的处理数据量和核算量，关于简略的操作，功用必定无法直接跟简略的聚合函数比较。所以，它们并不是简略的代替联系，而是不同需求和场景下挑选的技能和完成办法。

小结

本文从一个简略的Count函数运用示例出发，评论了聚合函数的相关概念。并展开评论了Count Distinct操作相关的各种技能优化和操作，以及怎么挑选适宜的处理计划，协助读者了解相关的数据库运用技能和工程化思维结构。