Small. Fast. Reliable.
Choose any three.
SQLite里的SQL
SQL As Understood By SQLite
[Top]
聚合函数
Aggregate Functions
下面所列的聚合函数默认都是可用的，用C些的附加聚合函数可以使用sqlite3_create_function()API添加进来。
The aggregate functions shown below are available by default. Additional
aggregate functions written in C may be added using the
sqlite3_create_function()
API.
每一个聚合函数都只有一个参数，这个参数可以加一个关键词DISTINCT前缀，这样，重复的元素就会在传入聚合函数之前被过滤掉。例如，函数"count(distinct X)" 会返回X列中不同的元素的数量，而不是X列中的非NULL元素的总数。
In any aggregate function that takes a single argument, that argument
can be preceded by the keyword DISTINCT. In such cases, duplicate
elements are filtered before being passed into the aggregate function.
For example, the function "count(distinct X)" will return the number
of distinct values of column X instead of the total number of non-null
values in column X.
|
avg(X) |
avg()函数返回一组非NULL的X的平均值。Sting和BLOB这两个不是数字的值会当做0处理。 只要输入包含至少一个非NULL的值，avg()返回的结果都会是一个浮点（float）数值。如果输入值全为NULL值，那么avg()会返回一个NULL。
The avg() function
returns the average value of all non-NULL X within a
group. String and BLOB values that do not look like numbers are
interpreted as 0.
The result of avg() is always a floating point value as long as
at there is at least one non-NULL input even if all
inputs are integers. The result of avg() is NULL if and only if
there are no non-NULL inputs.
|
count(X)
count(*) |
count(X)函数返回一组X中非NULL的值的个数。count(*)函数（不传入参数）返回一组结果的行数。
The count(X) function returns
a count of the number of times
that X is not NULL in a group. The count(*) function
(with no arguments) returns the total number of rows in the group.
|
group_concat(X)
group_concat(X,Y) |
group_concat()函数返回一个所有非NULLX连接在一起组成的字符串。如果传入了参数Y，那么会在字符串中使用Y参数分隔每一个X元素。如果忽略参数Y，那么会使用逗号(",")作为分隔符。连接的元素顺序是任意的。
The group_concat() function returns
a string which is the concatenation of
all non-NULL values of X. If parameter Y is present then
it is used as the separator
between instances of X. A comma (",") is used as the separator
if Y is omitted. The order of the concatenated elements is
arbitrary.
|
|
max(X) |
max()聚合函数会返回一组值中的最大值。最大值是指在这一列中坐ORDER BY后最末尾的值。如果传入数据没有非NULL的值，则会返回一个NULL。
The max() aggregate function
returns the maximum value of all values in the group.
The maximum value is the value that would be returned last in an
ORDER BY on the same column. Aggregate max() returns NULL
if and only if there are no non-NULL values in the group.
|
|
min(X) |
min()聚合函数会返回一组值中的最小的非NULL值。最小值是指在这一列中坐ORDER BY后第一个非NULL的值。如果传入数据没有非NULL的值，则会返回一个NULL。
The min() aggregate function
returns the minimum non-NULL value of all values in the group.
The minimum value is the first non-NULL value that would appear
in an ORDER BY of the column.
Aggregate min() returns NULL if and only if there are no non-NULL
values in the group.
|
sum(X)
total(X) |
sum()和total()聚合函数返回一组数据中所有非NULL元素的和值。如果输入全是NULL数据，那么sun()会返回NULL，而total()会返回0.0。通常没有输入数据时sum返回NULL是无益的，但是因为SQL标准是这么要求的，并且大部分其它的SQL数据库引擎也是这么实现的，所以SQLite为了兼容性也这么实现了。则针对这个SQL语言的设计问题，提供了一个非标准的函数total()以供使用。
The sum() and total() aggregate functions
return sum of all non-NULL values in the group.
If there are no non-NULL input rows then sum() returns
NULL but total() returns 0.0.
NULL is not normally a helpful result for the sum of no rows
but the SQL standard requires it and most other
SQL database engines implement sum() that way so SQLite does it in the
same way in order to be compatible. The non-standard total() function
is provided as a convenient way to work around this design problem
in the SQL language.
total()的总会返回一个浮点数（float）结果。而只要输入的非NULL数据都是整数（integer）的话，sum()会返回一个整数（integer）。如果输入数据含有浮点数，那么sum()会返回一个接近和值的浮点数。
The result of total() is always a floating point value.
The result of sum() is an integer value if all non-NULL inputs are integers.
If any input to sum() is neither an integer or a NULL
then sum() returns a floating point value
which might be an approximation to the true sum.
如果输入内容只包含整数和NULL，并且在计算过程中发生了整数溢出，那么sum()会返回一个 "integer overflow" 异常。total()则从来不会返回 "integer overflow" 。
Sum() will throw an "integer overflow" exception if all inputs
are integers or NULL
and an integer overflow occurs at any point during the computation.
Total() never throws an integer overflow.
|