This is an automated email from the ASF dual-hosted git repository. panxiaolei pushed a commit to branch dev_0828 in repository https://gitbox.apache.org/repos/asf/doris-website.git
commit c746030586394c47e33192e26220d9682d2b6b22 Author: BiteTheDDDDt <[email protected]> AuthorDate: Thu Aug 28 15:48:12 2025 +0800 update bitmap/topn series doc --- .../aggregate-functions/bitmap-agg.md | 71 ++++++++---- .../aggregate-functions/bitmap-intersect.md | 60 +++++++--- .../aggregate-functions/bitmap-union-count.md | 54 +++++---- .../aggregate-functions/bitmap-union-int.md | 44 +++++--- .../aggregate-functions/bitmap-union.md | 121 ++++++--------------- .../aggregate-functions/topn-array.md | 32 ++++-- .../aggregate-functions/topn-weighted.md | 37 +++++-- .../sql-functions/aggregate-functions/topn.md | 35 ++++-- .../aggregate-functions/bitmap-agg.md | 56 ++++++---- .../aggregate-functions/bitmap-intersect.md | 57 +++++++--- .../aggregate-functions/bitmap-union-count.md | 50 ++++++--- .../aggregate-functions/bitmap-union-int.md | 43 +++++--- .../aggregate-functions/bitmap-union.md | 118 ++++++-------------- .../aggregate-functions/topn-array.md | 30 +++-- .../aggregate-functions/topn-weighted.md | 35 ++++-- .../sql-functions/aggregate-functions/topn.md | 30 +++-- 16 files changed, 507 insertions(+), 366 deletions(-) diff --git a/docs/sql-manual/sql-functions/aggregate-functions/bitmap-agg.md b/docs/sql-manual/sql-functions/aggregate-functions/bitmap-agg.md index 640196fea5a..420d3196981 100644 --- a/docs/sql-manual/sql-functions/aggregate-functions/bitmap-agg.md +++ b/docs/sql-manual/sql-functions/aggregate-functions/bitmap-agg.md @@ -7,7 +7,8 @@ ## Description -Aggregate the values of a column (excluding any NULL values) and return a single row bitmap value, i.e., convert multiple rows into one. +Aggregates the non-NULL values of the input expression into a Bitmap. +If a value is less than 0 or greater than 18446744073709551615, it will be ignored and not merged into the Bitmap. ## Syntax @@ -15,36 +16,38 @@ Aggregate the values of a column (excluding any NULL values) and return a single BITMAP_AGG(<expr>) ``` -## Parameters +## Arguments -| Parameter | Description | +| Argument | Description | | -- | -- | -| `<expr>` | The column or expression of values to be aggregated. The type of expr must be TINYINT, SMALLINT, INT, LARGEINT, or BIGINT, and it also supports VARCHAR that can be converted to one of these types. | +| `<expr>` | The column or expression to be aggregated. Supported types: TinyInt, SmallInt, Integer, BigInt. | ## Return Value -Returns a BITMAP type value. Special cases: - -- If a value is less than 0 or greater than 18446744073709551615, the value will be ignored and will not be merged into the Bitmap. +Returns a value of Bitmap type. If there is no valid data in the group, returns an empty Bitmap. ## Example ```sql -select * from test_bitmap_agg; -``` - -```text -+------+------+------+------+------+-------------+----------------------+ -| id | k0 | k1 | k2 | k3 | k4 | k5 | -+------+------+------+------+------+-------------+----------------------+ -| 1 | 10 | 110 | 11 | 300 | 10000000000 | 0 | -| 2 | 20 | 120 | 21 | 400 | 20000000000 | 200000000000000 | -| 3 | 30 | 130 | 31 | 350 | 30000000000 | 300000000000000 | -| 4 | 40 | 140 | 41 | 500 | 40000000000 | 18446744073709551616 | -| 5 | 50 | 150 | 51 | 250 | 50000000000 | 18446744073709551615 | -| 6 | 60 | 160 | 61 | 600 | 60000000000 | -1 | -| 7 | 60 | 160 | 120 | 600 | 60000000000 | NULL | -+------+------+------+------+------+-------------+----------------------+ +-- setup +CREATE TABLE test_bitmap_agg ( + id INT, + k0 INT, + k1 INT, + k2 INT, + k3 INT, + k4 BIGINT, + k5 BIGINT +) DISTRIBUTED BY HASH(id) BUCKETS 1 +PROPERTIES ("replication_num" = "1"); +INSERT INTO test_bitmap_agg VALUES + (1, 10, 110, 11, 300, 10000000000, 0), + (2, 20, 120, 21, 400, 20000000000, 200000000000000), + (3, 30, 130, 31, 350, 30000000000, 300000000000000), + (4, 40, 140, 41, 500, 40000000000, 18446744073709551616), + (5, 50, 150, 51, 250, 50000000000, 18446744073709551615), + (6, 60, 160, 61, 600, 60000000000, -1), + (7, 60, 160, 120, 600, 60000000000, NULL); ``` ```sql @@ -63,6 +66,30 @@ select bitmap_to_string(bitmap_agg(k0)) from test_bitmap_agg; select bitmap_to_string(bitmap_agg(k5)) from test_bitmap_agg; ``` +```text ++--------------------------------------------------------+ +| bitmap_to_string(bitmap_agg(k5)) | ++--------------------------------------------------------+ +| 0,200000000000000,300000000000000,18446744073709551615 | ++--------------------------------------------------------+ +``` + +```sql +select bitmap_to_string(bitmap_agg(k5)) from test_bitmap_agg where k5 is null; +``` + +```text ++----------------------------------+ +| bitmap_to_string(bitmap_agg(k5)) | ++----------------------------------+ +| | ++----------------------------------+ +``` + +```sql +select bitmap_to_string(bitmap_agg(k5)) from test_bitmap_agg; +``` + ```text +--------------------------------------------------------+ | bitmap_to_string(bitmap_agg(cast(k5 as BIGINT))) | diff --git a/docs/sql-manual/sql-functions/aggregate-functions/bitmap-intersect.md b/docs/sql-manual/sql-functions/aggregate-functions/bitmap-intersect.md index eece7190074..70fac92ceab 100644 --- a/docs/sql-manual/sql-functions/aggregate-functions/bitmap-intersect.md +++ b/docs/sql-manual/sql-functions/aggregate-functions/bitmap-intersect.md @@ -7,7 +7,7 @@ ## Description -Aggregation function, used to calculate the bitmap intersection after grouping. Common usage scenarios such as: calculating user retention rate. +Used to calculate the intersection of grouped Bitmaps. Common use case: calculating user retention. ## Syntax @@ -15,33 +15,63 @@ Aggregation function, used to calculate the bitmap intersection after grouping. BITMAP_INTERSECT(BITMAP <value>) ``` -## Parameters +## Arguments -| Parameter | Description | +| Argument | Description | | -- | -- | -| `<value>` | Supported bitmap data types | +| `<value>` | Data type supporting Bitmap | ## Return Value -The data type of the return value is BITMAP. +Returns a value of Bitmap type. If there is no valid data in the group, returns NULL. ## Example -Table schema +## Example -``` -KeysType: AGG_KEY -Columns: tag varchar, date datetime, user_id bitmap bitmap_union +```sql +-- setup +CREATE TABLE user_tags ( + tag VARCHAR(20), + date DATETIME, + user_id BITMAP bitmap_union +) AGGREGATE KEY(tag, date) DISTRIBUTED BY HASH(tag) BUCKETS 1 +PROPERTIES ("replication_num" = "1"); +INSERT INTO user_tags VALUES + ('A', '2020-05-18', to_bitmap(1)), + ('A', '2020-05-18', to_bitmap(2)), + ('A', '2020-05-19', to_bitmap(2)), + ('A', '2020-05-19', to_bitmap(3)), + ('B', '2020-05-18', to_bitmap(4)), + ('B', '2020-05-19', to_bitmap(4)), + ('B', '2020-05-19', to_bitmap(5)); ``` -``` -Find the retention of users between 2020-05-18 and 2020-05-19 under different tags. -mysql> select tag, bitmap_intersect(user_id) from (select tag, date, bitmap_union(user_id) user_id from table where date in ('2020-05-18', '2020-05-19') group by tag, date) a group by tag; +```sql +select tag, bitmap_to_string(bitmap_intersect(user_id)) from ( + select tag, date, bitmap_union(user_id) user_id from user_tags where date in ('2020-05-18', '2020-05-19') group by tag, date +) a group by tag; ``` -Used in combination with the bitmap_to_string function to obtain the specific data of the intersection +Query the user retention for different tags between today and yesterday. +```text ++------+---------------------------------------------+ +| tag | bitmap_to_string(bitmap_intersect(user_id)) | ++------+---------------------------------------------+ +| A | 2 | +| B | 4 | ++------+---------------------------------------------+ ``` -Who are the users retained under different tags between 2020-05-18 and 2020-05-19? -mysql> select tag, bitmap_to_string(bitmap_intersect(user_id)) from (select tag, date, bitmap_union(user_id) user_id from table where date in ('2020-05-18', '2020-05-19') group by tag, date) a group by tag; + +```sql +select bitmap_to_string(bitmap_intersect(user_id)) from user_tags where tag is null; +``` + +```text ++---------------------------------------------+ +| bitmap_to_string(bitmap_intersect(user_id)) | ++---------------------------------------------+ +| | ++---------------------------------------------+ ``` diff --git a/docs/sql-manual/sql-functions/aggregate-functions/bitmap-union-count.md b/docs/sql-manual/sql-functions/aggregate-functions/bitmap-union-count.md index 554c91def61..e283ed445af 100644 --- a/docs/sql-manual/sql-functions/aggregate-functions/bitmap-union-count.md +++ b/docs/sql-manual/sql-functions/aggregate-functions/bitmap-union-count.md @@ -7,7 +7,7 @@ ## Description -Computes the union of input Bitmaps and returns their cardinality. +Calculates the union of input Bitmaps and returns its cardinality. ## Syntax @@ -15,41 +15,55 @@ Computes the union of input Bitmaps and returns their cardinality. BITMAP_UNION_COUNT(<expr>) ``` -## Parameters +## Arguments -| Parameter | Description | +| Argument | Description | | -- | -- | -| `<expr>` | Supported data types of BITMAP | +| `<expr>` | Data type supporting Bitmap | ## Return Value -Returns the size of the Bitmap union, that is, the number of elements after deduplication +Returns the size of the Bitmap union, i.e., the number of distinct elements. If there is no valid data in the group, returns 0. ## Example ```sql -select dt,page,bitmap_to_string(user_id) from pv_bitmap; +-- setup +CREATE TABLE pv_bitmap ( + dt INT, + page INT, + user_id BITMAP +) DISTRIBUTED BY HASH(dt) BUCKETS 1 +PROPERTIES ("replication_num" = "1"); +INSERT INTO pv_bitmap VALUES + (1, 100, to_bitmap(100)), + (1, 100, to_bitmap(200)), + (1, 100, to_bitmap(300)), + (2, 200, to_bitmap(300)); ``` -```text -+------+------+---------------------------+ -| dt | page | bitmap_to_string(user_id) | -+------+------+---------------------------+ -| 1 | 100 | 100,200,300 | -| 2 | 200 | 300 | -+------+------+---------------------------+ +```sql +select bitmap_union_count(user_id) from pv_bitmap; ``` -Calculate the deduplication value of user_id: +Counts the number of distinct user_id values. +```text ++-----------------------------+ +| bitmap_union_count(user_id) | ++-----------------------------+ +| 3 | ++-----------------------------+ ``` -select bitmap_union_count(user_id) from pv_bitmap; + +```sql +select bitmap_union_count(user_id) from pv_bitmap where user_id is null; ``` ```text -+-------------------------------------+ -| bitmap_count(bitmap_union(user_id)) | -+-------------------------------------+ -| 3 | -+-------------------------------------+ ++-----------------------------+ +| bitmap_union_count(user_id) | ++-----------------------------+ +| 0 | ++-----------------------------+ ``` diff --git a/docs/sql-manual/sql-functions/aggregate-functions/bitmap-union-int.md b/docs/sql-manual/sql-functions/aggregate-functions/bitmap-union-int.md index 36b1d397b53..da4e85a2141 100644 --- a/docs/sql-manual/sql-functions/aggregate-functions/bitmap-union-int.md +++ b/docs/sql-manual/sql-functions/aggregate-functions/bitmap-union-int.md @@ -7,7 +7,7 @@ ## Description -Counts the number of distinct values in columns of type TINYINT, SMALLINT and INT. The return value is the same as COUNT(DISTINCT expr) +Counts the number of distinct values in the input expression. The return value is the same as COUNT(DISTINCT expr). ## Syntax @@ -15,40 +15,54 @@ Counts the number of distinct values in columns of type TINYINT, SMALLINT and IN BITMAP_UNION_INT(<expr>) ``` -## Parameters +## Arguments -| Parameter | Description | +| Argument | Description | | -- | -- | -| `<expr>` | Supports columns or column expressions of type TINYINT, SMALLINT and INT | +| `<expr>` | The input expression. Supported types: TinyInt, SmallInt, Integer. | ## Return Value -Returns the number of distinct values in a column. +Returns the number of distinct values in the column. If there is no valid data in the group, returns 0. ## Example ```sql -select dt,page,bitmap_to_string(user_id) from pv_bitmap; +-- setup +CREATE TABLE pv_bitmap ( + dt INT, + page INT, + user_id BITMAP +) DISTRIBUTED BY HASH(dt) BUCKETS 1 +PROPERTIES ("replication_num" = "1"); +INSERT INTO pv_bitmap VALUES + (1, 100, to_bitmap(100)), + (1, 100, to_bitmap(200)), + (1, 100, to_bitmap(300)), + (1, 300, to_bitmap(300)), + (2, 200, to_bitmap(300)); +``` + +```sql +select bitmap_union_int(dt) from pv_bitmap; ``` ```text -+------+------+---------------------------+ -| dt | page | bitmap_to_string(user_id) | -+------+------+---------------------------+ -| 1 | 100 | 100,200,300 | -| 1 | 300 | 300 | -| 2 | 200 | 300 | -+------+------+---------------------------+ ++----------------------+ +| bitmap_union_int(dt) | ++----------------------+ +| 2 | ++----------------------+ ``` ```sql -select bitmap_union_int(dt) from pv_bitmap; +select bitmap_union_int(dt) from pv_bitmap where dt is null; ``` ```text +----------------------+ | bitmap_union_int(dt) | +----------------------+ -| 2 | +| 0 | +----------------------+ ``` \ No newline at end of file diff --git a/docs/sql-manual/sql-functions/aggregate-functions/bitmap-union.md b/docs/sql-manual/sql-functions/aggregate-functions/bitmap-union.md index 14db187440a..30e413e433e 100644 --- a/docs/sql-manual/sql-functions/aggregate-functions/bitmap-union.md +++ b/docs/sql-manual/sql-functions/aggregate-functions/bitmap-union.md @@ -7,7 +7,7 @@ ## Description -Calculate the union of input Bitmaps and return a new bitmap +Calculates the union of input Bitmaps and returns a new Bitmap. ## Syntax @@ -15,110 +15,53 @@ Calculate the union of input Bitmaps and return a new bitmap BITMAP_UNION(<expr>) ``` -## Parameters +## Arguments -| Parameter | Description | +| Argument | Description | | -- | -- | -| `<expr>` | Supported data types of BITMAP | +| `<expr>` | Data type supporting Bitmap | ## Return Value -The data type of the return value is BITMAP. +Returns a value of Bitmap type. If there is no valid data in the group, returns an empty Bitmap. ## Example ```sql -select dt,page,bitmap_to_string(user_id) from pv_bitmap; +-- setup +CREATE TABLE pv_bitmap ( + dt INT, + page INT, + user_id BITMAP +) DISTRIBUTED BY HASH(dt) BUCKETS 1 +PROPERTIES ("replication_num" = "1"); +INSERT INTO pv_bitmap VALUES + (1, 100, to_bitmap(100)), + (1, 100, to_bitmap(200)), + (1, 100, to_bitmap(300)), + (2, 200, to_bitmap(300)); ``` -```text -+------+------+---------------------------+ -| dt | page | bitmap_to_string(user_id) | -+------+------+---------------------------+ -| 1 | 100 | 100,200,300 | -| 2 | 200 | 300 | -+------+------+---------------------------+ -``` - -Calculate the deduplication value of user_id: - -``` -select bitmap_count(bitmap_union(user_id)) from pv_bitmap; +```sql +select bitmap_to_string(bitmap_union(user_id)) from pv_bitmap; ``` ```text -+-------------------------------------+ -| bitmap_count(bitmap_union(user_id)) | -+-------------------------------------+ -| 3 | -+-------------------------------------+ -``` - -### Create table - -The aggregation model needs to be used when creating the table. The data type is bitmap and the aggregation function is bitmap_union. -``` -CREATE TABLE `pv_bitmap` ( - `dt` int (11) NULL COMMENT" ", - `page` varchar (10) NULL COMMENT" ", - `user_id` bitmap BITMAP_UNION NULL COMMENT" " -) ENGINE = OLAP -AGGREGATE KEY (`dt`,` page`) -COMMENT "OLAP" -DISTRIBUTED BY HASH (`dt`) BUCKETS 2; -``` - -Note: When the amount of data is large, it is best to create a corresponding rollup table for high-frequency bitmap_union queries - -``` -ALTER TABLE pv_bitmap ADD ROLLUP pv (page, user_id); -``` - -### Data Load - -`TO_BITMAP (expr)`: Convert 0 ~ 18446744073709551615 unsigned bigint to bitmap - -`BITMAP_EMPTY ()`: Generate empty bitmap columns, used for insert or import to fill the default value - -`BITMAP_HASH (expr)` or `BITMAP_HASH64 (expr)`: Convert any type of column to a bitmap by hashing - -#### Stream Load - -``` -cat data | curl --location-trusted -u user: passwd -T--H "columns: dt, page, user_id, user_id = to_bitmap (user_id)" http: // host: 8410 / api / test / testDb / _stream_load -``` - -``` -cat data | curl --location-trusted -u user: passwd -T--H "columns: dt, page, user_id, user_id = bitmap_hash (user_id)" http: // host: 8410 / api / test / testDb / _stream_load -``` - -``` -cat data | curl --location-trusted -u user: passwd -T--H "columns: dt, page, user_id, user_id = bitmap_empty ()" http: // host: 8410 / api / test / testDb / _stream_load -``` - -#### Insert Into - -id2's column type is bitmap -``` -insert into bitmap_table1 select id, id2 from bitmap_table2; ++-----------------------------------------+ +| bitmap_to_string(bitmap_union(user_id)) | ++-----------------------------------------+ +| 100,200,300 | ++-----------------------------------------+ ``` -id2's column type is bitmap -``` -INSERT INTO bitmap_table1 (id, id2) VALUES (1001, to_bitmap (1000)), (1001, to_bitmap (2000)); -``` - -id2's column type is bitmap -``` -insert into bitmap_table1 select id, bitmap_union (id2) from bitmap_table2 group by id; -``` - -id2's column type is int -``` -insert into bitmap_table1 select id, to_bitmap (id2) from table; +```sql +select bitmap_to_string(bitmap_union(user_id)) from pv_bitmap where user_id is null; ``` -id2's column type is String -``` -insert into bitmap_table1 select id, bitmap_hash (id_string) from table; +```text ++-----------------------------------------+ +| bitmap_to_string(bitmap_union(user_id)) | ++-----------------------------------------+ +| | ++-----------------------------------------+ ``` diff --git a/docs/sql-manual/sql-functions/aggregate-functions/topn-array.md b/docs/sql-manual/sql-functions/aggregate-functions/topn-array.md index 7e84206a57a..3e9d96bfb7e 100644 --- a/docs/sql-manual/sql-functions/aggregate-functions/topn-array.md +++ b/docs/sql-manual/sql-functions/aggregate-functions/topn-array.md @@ -19,17 +19,18 @@ TOPN_ARRAY(<expr>, <top_num> [, <space_expand_rate>]) | Parameter | Description | | -- | -- | -| `<expr>` | The column or expression to be counted. | -| `<top_num>` | The number of the most frequent values to return. It must be a positive integer. | -| `<space_expand_rate>` | Optional parameter, which is used to set the number of counters used in the Space-Saving algorithm. `counter_numbers = top_num * space_expand_rate` , the larger the value of space_expand_rate, the more accurate the result, and the default value is 50. | +| `<expr>` | The column or expression to be counted. Supported types: TinyInt, SmallInt, Integer, BigInt, LargeInt, Float, Double, Decimal, Date, Datetime, IPV4, IPV6, String. | +| `<top_num>` | The number of most frequent values to return. Must be a positive integer. Supported type: Integer. | +| `<space_expand_rate>` | Optional. Sets the number of counters used in the Space-Saving algorithm: `counter_numbers = top_num * space_expand_rate`. The larger the value, the more accurate the result. Default is 50. Supported type: Integer. | ## Return Value -Return an array containing the N most frequent values. +Returns an array containing the N most frequent values. +If there is no valid data in the group, returns NULL. -## Examples +## Example ```sql --- Create sample table +-- setup CREATE TABLE page_visits ( page_id INT, user_id INT, @@ -38,8 +39,6 @@ CREATE TABLE page_visits ( PROPERTIES ( "replication_num" = "1" ); - --- Insert test data INSERT INTO page_visits VALUES (1, 101, '2024-01-01'), (2, 102, '2024-01-01'), @@ -49,12 +48,15 @@ INSERT INTO page_visits VALUES (2, 105, '2024-01-01'), (1, 106, '2024-01-01'), (4, 107, '2024-01-01'); +``` --- Find top 3 most visited pages +```sql SELECT TOPN_ARRAY(page_id, 3) as top_pages FROM page_visits; ``` +Find the top 3 most visited pages. + ```text +-----------+ | top_pages | @@ -62,3 +64,15 @@ FROM page_visits; | [1, 2, 4] | +-----------+ ``` + +```sql +SELECT TOPN_ARRAY(page_id, 3) as top_pages FROM page_visits where page_id is null; +``` + +```text ++-----------+ +| top_pages | ++-----------+ +| NULL | ++-----------+ +``` diff --git a/docs/sql-manual/sql-functions/aggregate-functions/topn-weighted.md b/docs/sql-manual/sql-functions/aggregate-functions/topn-weighted.md index 3b4cf33d595..0192b027787 100644 --- a/docs/sql-manual/sql-functions/aggregate-functions/topn-weighted.md +++ b/docs/sql-manual/sql-functions/aggregate-functions/topn-weighted.md @@ -19,18 +19,19 @@ TOPN_WEIGHTED(<expr>, <weight>, <top_num> [, <space_expand_rate>]) | Parameter | Description | | -- | -- | -| `<expr>` | The column or expression to be counted | -| `<weight>` | The column or expression to adjust the weight | -| `<top_num>` | The number of the most frequent values to return. It must be a positive integer. | -| `<space_expand_rate>` | Optional, the value to set the counter_numbers used in the Space-Saving algorithm. `counter_numbers = top_num * space_expand_rate`. The value of space_expand_rate should be greater than 1, and the default value is 50. | +| `<expr>` | The column or expression to be counted. Supported types: TinyInt, SmallInt, Integer, BigInt, LargeInt, Float, Double, Decimal, Date, Datetime, IPV4, IPV6, String. | +| `<weight>` | The column or expression used to adjust the weight. Supported type: Double.| +| `<top_num>` | The number of most frequent values to return. Must be a positive integer. Supported type: Integer. | +| `<space_expand_rate>` | Optional. Sets the number of counters used in the Space-Saving algorithm: `counter_numbers = top_num * space_expand_rate`. The larger the value, the more accurate the result. Default is 50. Supported type: Integer. | ## Return Value -Return an array containing values and weighted counts. +Returns an array containing the N values with the highest weighted counts. +If there is no valid data in the group, returns NULL. -## Examples +## Example ```sql --- create example table +-- setup CREATE TABLE product_sales ( product_id INT, sale_amount DECIMAL(10,2), @@ -39,8 +40,6 @@ CREATE TABLE product_sales ( PROPERTIES ( "replication_num" = "1" ); - --- insert test data INSERT INTO product_sales VALUES (1, 100.00, '2024-01-01'), (2, 50.00, '2024-01-01'), @@ -50,12 +49,15 @@ INSERT INTO product_sales VALUES (2, 80.00, '2024-01-01'), (1, 120.00, '2024-01-01'), (4, 90.00, '2024-01-01'); +``` --- find the top 3 products with highest sales amount +```sql SELECT TOPN_WEIGHTED(product_id, sale_amount, 3) as top_products FROM product_sales; ``` +Find the top 3 products by sales amount (weighted). + ```text +--------------+ | top_products | @@ -63,3 +65,18 @@ FROM product_sales; | [1, 2, 4] | +--------------+ ``` + +```sql +SELECT TOPN_WEIGHTED(product_id, sale_amount, 3) as top_products +FROM product_sales where product_id is null; +``` + +Find the top 3 products by sales amount (weighted). + +```text ++--------------+ +| top_products | ++--------------+ +| NULL | ++--------------+ +``` diff --git a/docs/sql-manual/sql-functions/aggregate-functions/topn.md b/docs/sql-manual/sql-functions/aggregate-functions/topn.md index d107b307598..6318eb1d75c 100644 --- a/docs/sql-manual/sql-functions/aggregate-functions/topn.md +++ b/docs/sql-manual/sql-functions/aggregate-functions/topn.md @@ -18,17 +18,18 @@ TOPN(<expr>, <top_num> [, <space_expand_rate>]) ## Parameters | Parameter | Description | | -- | -- | -| `<expr>` | The column or expression to be counted. | -| `<top_num>` | The number of the most frequent values to return. It must be a positive integer. | -| `<space_expand_rate>` | Optional parameter, which is used to set the number of counters used in the Space-Saving algorithm. `counter_numbers = top_num * space_expand_rate` , the larger the value of space_expand_rate, the more accurate the result, and the default value is 50. | +| `<expr>` | The column or expression to be counted. Supported types: TinyInt, SmallInt, Integer, BigInt, LargeInt, Float, Double, Decimal, Date, Datetime, IPV4, IPV6, String. | +| `<top_num>` | The number of most frequent values to return. Must be a positive integer. Supported type: Integer. | +| `<space_expand_rate>` | Optional. Sets the number of counters used in the Space-Saving algorithm: `counter_numbers = top_num * space_expand_rate`. The larger the value, the more accurate the result. Default is 50. Supported type: Integer. | ## Return Value -Returns a JSON string containing values and their corresponding occurrence counts. +Returns a JSON string containing the values and their counts. +If there is no valid data in the group, returns NULL. -## Examples +## Example ```sql --- Create sample table +-- setup CREATE TABLE page_visits ( page_id INT, user_id INT, @@ -37,8 +38,6 @@ CREATE TABLE page_visits ( PROPERTIES ( "replication_num" = "1" ); - --- Insert test data INSERT INTO page_visits VALUES (1, 101, '2024-01-01'), (2, 102, '2024-01-01'), @@ -48,16 +47,32 @@ INSERT INTO page_visits VALUES (2, 105, '2024-01-01'), (1, 106, '2024-01-01'), (4, 107, '2024-01-01'); +``` --- Find top 3 most visited pages +```sql SELECT TOPN(page_id, 3) as top_pages FROM page_visits; ``` +Find the top 3 most visited pages. + ```text +---------------------+ | top_pages | +---------------------+ | {"1":4,"2":2,"4":1} | +---------------------+ -``` \ No newline at end of file +``` + +```sql +SELECT TOPN(page_id, 3) as top_pages +FROM page_visits where page_id is null; +``` + +```text ++-----------+ +| top_pages | ++-----------+ +| NULL | ++-----------+ +``` diff --git a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/bitmap-agg.md b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/bitmap-agg.md index 82da00deb80..de6a9b61a2e 100644 --- a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/bitmap-agg.md +++ b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/bitmap-agg.md @@ -7,7 +7,8 @@ ## 描述 -聚合某列的值（不包括任何空值）返回一行 bitmap 值，即多行转一行。 +将输入的表达式聚合的非 NULL 值聚合为一个 Bitmap 。 +如果某个值小于 0 或者大于 18446744073709551615，该值会被忽略，不会合并到 Bitmap 中。 ## 语法 @@ -19,32 +20,35 @@ BITMAP_AGG(<expr>) | 参数 | 说明 | | -- | -- | -| `<expr>` |待合并数值的列或表达式，expr 的类型需要为 TINYINT,SMALLINT,INT,LARGEINT 和 BIGINT 类型，也支持可以转化为以上类型的 VARCHAR。 | +| `<expr>` |待合并数值的列或表达式，支持类型为 TinyInt，SmallInt，Integer，BigInt。 | ## 返回值 -返回 BITMAP 类型的值。特殊情况： - -- 如果某个值小于 0 或者大于 18446744073709551615，该值会被忽略，不会合并到 Bitmap 中 +返回 Bitmap 类型的值。如果组内没有合法数据，则返回空 Bitmap 。 ## 举例 ```sql -select * from test_bitmap_agg; +-- setup +CREATE TABLE test_bitmap_agg ( + id INT, + k0 INT, + k1 INT, + k2 INT, + k3 INT, + k4 BIGINT, + k5 BIGINT +) DISTRIBUTED BY HASH(id) BUCKETS 1 +PROPERTIES ("replication_num" = "1"); +INSERT INTO test_bitmap_agg VALUES + (1, 10, 110, 11, 300, 10000000000, 0), + (2, 20, 120, 21, 400, 20000000000, 200000000000000), + (3, 30, 130, 31, 350, 30000000000, 300000000000000), + (4, 40, 140, 41, 500, 40000000000, 18446744073709551616), + (5, 50, 150, 51, 250, 50000000000, 18446744073709551615), + (6, 60, 160, 61, 600, 60000000000, -1), + (7, 60, 160, 120, 600, 60000000000, NULL); ``` -```text -+------+------+------+------+------+-------------+----------------------+ -| id | k0 | k1 | k2 | k3 | k4 | k5 | -+------+------+------+------+------+-------------+----------------------+ -| 1 | 10 | 110 | 11 | 300 | 10000000000 | 0 | -| 2 | 20 | 120 | 21 | 400 | 20000000000 | 200000000000000 | -| 3 | 30 | 130 | 31 | 350 | 30000000000 | 300000000000000 | -| 4 | 40 | 140 | 41 | 500 | 40000000000 | 18446744073709551616 | -| 5 | 50 | 150 | 51 | 250 | 50000000000 | 18446744073709551615 | -| 6 | 60 | 160 | 61 | 600 | 60000000000 | -1 | -| 7 | 60 | 160 | 120 | 600 | 60000000000 | NULL | -+------+------+------+------+------+-------------+----------------------+ -``` ```sql select bitmap_to_string(bitmap_agg(k0)) from test_bitmap_agg; @@ -64,8 +68,20 @@ select bitmap_to_string(bitmap_agg(k5)) from test_bitmap_agg; ```text +--------------------------------------------------------+ -| bitmap_to_string(bitmap_agg(cast(k5 as BIGINT))) | +| bitmap_to_string(bitmap_agg(k5)) | +--------------------------------------------------------+ | 0,200000000000000,300000000000000,18446744073709551615 | +--------------------------------------------------------+ ``` + +```sql +select bitmap_to_string(bitmap_agg(k5)) from test_bitmap_agg where k5 is null; +``` + +```text ++----------------------------------+ +| bitmap_to_string(bitmap_agg(k5)) | ++----------------------------------+ +| | ++----------------------------------+ +``` diff --git a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/bitmap-intersect.md b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/bitmap-intersect.md index 9cb01c2f52a..4021d4b550a 100644 --- a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/bitmap-intersect.md +++ b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/bitmap-intersect.md @@ -7,7 +7,7 @@ ## 描述 -聚合函数，用于计算分组后的 bitmap 交集。常见使用场景如：计算用户留存率。 +用于计算分组后的 Bitmap 交集。常见使用场景如：计算用户留存率。 ## 语法 @@ -19,31 +19,58 @@ BITMAP_INTERSECT(BITMAP <value>) | 参数 | 说明 | | -- | -- | -| `<value>` | 支持 bitmap 的数据类型 | +| `<value>` | 支持 Bitmap 的数据类型 | ## 返回值 -返回值的数据类型为 BITMAP。 +返回值的数据类型为 Bitmap。 +组内没有合法数据时，返回 NULL。 ## 举例 -表结构 - -``` -KeysType: AGG_KEY -Columns: tag varchar, date datetime, user_id bitmap bitmap_union - +```sql +-- setup +CREATE TABLE user_tags ( + tag VARCHAR(20), + date DATETIME, + user_id BITMAP bitmap_union +) AGGREGATE KEY(tag, date) DISTRIBUTED BY HASH(tag) BUCKETS 1 +PROPERTIES ("replication_num" = "1"); +INSERT INTO user_tags VALUES + ('A', '2020-05-18', to_bitmap(1)), + ('A', '2020-05-18', to_bitmap(2)), + ('A', '2020-05-19', to_bitmap(2)), + ('A', '2020-05-19', to_bitmap(3)), + ('B', '2020-05-18', to_bitmap(4)), + ('B', '2020-05-19', to_bitmap(4)), + ('B', '2020-05-19', to_bitmap(5)); ``` -``` -求今天和昨天不同 tag 下的用户留存 -select tag, bitmap_intersect(user_id) from (select tag, date, bitmap_union(user_id) user_id from table where date in ('2020-05-18', '2020-05-19') group by tag, date) a group by tag; +```sql +select tag, bitmap_to_string(bitmap_intersect(user_id)) from ( + select tag, date, bitmap_union(user_id) user_id from user_tags where date in ('2020-05-18', '2020-05-19') group by tag, date +) a group by tag; ``` -和 bitmap_to_string 函数组合使用可以获取交集的具体数据 +查询今天和昨天不同 tag 下的用户留存。 +```text ++------+---------------------------------------------+ +| tag | bitmap_to_string(bitmap_intersect(user_id)) | ++------+---------------------------------------------+ +| A | 2 | +| B | 4 | ++------+---------------------------------------------+ ``` -求今天和昨天不同 tag 下留存的用户都是哪些 -select tag, bitmap_to_string(bitmap_intersect(user_id)) from (select tag, date, bitmap_union(user_id) user_id from table where date in ('2020-05-18', '2020-05-19') group by tag, date) a group by tag; + +```sql +select bitmap_to_string(bitmap_intersect(user_id)) from user_tags where tag is null; ``` +```text ++---------------------------------------------+ +| bitmap_to_string(bitmap_intersect(user_id)) | ++---------------------------------------------+ +| | ++---------------------------------------------+ +``` \ No newline at end of file diff --git a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/bitmap-union-count.md b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/bitmap-union-count.md index ccb0510f49b..ce9212e622c 100644 --- a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/bitmap-union-count.md +++ b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/bitmap-union-count.md @@ -19,37 +19,53 @@ BITMAP_UNION_COUNT(<expr>) | 参数 | 说明 | | -- | -- | -| `<expr>` | 支持 BITMAP 的数据类型 | +| `<expr>` | 支持 Bitmap 的数据类型 | ## 返回值 -返回 Bitmap 并集的大小，即去重后的元素个数 +返回 Bitmap 并集的大小，即去重后的元素个数。 +组内没有合法数据时，返回 0 。 ## 举例 ```sql -select dt,page,bitmap_to_string(user_id) from pv_bitmap; +-- setup +CREATE TABLE pv_bitmap ( + dt INT, + page INT, + user_id BITMAP +) DISTRIBUTED BY HASH(dt) BUCKETS 1 +PROPERTIES ("replication_num" = "1"); +INSERT INTO pv_bitmap VALUES + (1, 100, to_bitmap(100)), + (1, 100, to_bitmap(200)), + (1, 100, to_bitmap(300)), + (2, 200, to_bitmap(300)); ``` -```text -+------+------+---------------------------+ -| dt | page | bitmap_to_string(user_id) | -+------+------+---------------------------+ -| 1 | 100 | 100,200,300 | -| 2 | 200 | 300 | -+------+------+---------------------------+ +```sql +select bitmap_union_count(user_id) from pv_bitmap; ``` -计算 user_id 的去重值： +计算 user_id 的去重值个数。 +```text ++-----------------------------+ +| bitmap_union_count(user_id) | ++-----------------------------+ +| 3 | ++-----------------------------+ ``` -select bitmap_union_count(user_id) from pv_bitmap; + +```sql +select bitmap_union_count(user_id) from pv_bitmap where user_id is null; ``` ```text -+-------------------------------------+ -| bitmap_count(bitmap_union(user_id)) | -+-------------------------------------+ -| 3 | -+-------------------------------------+ ++-----------------------------+ +| bitmap_union_count(user_id) | ++-----------------------------+ +| 0 | ++-----------------------------+ ``` + diff --git a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/bitmap-union-int.md b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/bitmap-union-int.md index bab62c48b24..b5d4fe6d0e3 100644 --- a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/bitmap-union-int.md +++ b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/bitmap-union-int.md @@ -7,7 +7,7 @@ ## 描述 -计算 TINYINT,SMALLINT 和 INT 类型的列中不同值的个数，返回值和 COUNT(DISTINCT expr) 相同 +计算输入的表达式中不同值的个数，返回值和 COUNT(DISTINCT expr) 相同。 ## 语法 @@ -19,36 +19,51 @@ BITMAP_UNION_INT(<expr>) | 参数 | 说明 | | -- | -- | -| `<expr>` | 支持 TINYINT，SMALLINT 和 INT 类型的列或列表达式 | +| `<expr>` | 输入的表达式，支持类型为 TinyInt，SmallInt，Integer。 | ## 返回值 -返回列中不同值的个数 +返回列中不同值的个数。 +组内没有合法数据时，返回 0 。 ## 举例 ```sql -select dt,page,bitmap_to_string(user_id) from pv_bitmap; +-- setup +CREATE TABLE pv_bitmap ( + dt INT, + page INT, + user_id BITMAP +) DISTRIBUTED BY HASH(dt) BUCKETS 1 +PROPERTIES ("replication_num" = "1"); +INSERT INTO pv_bitmap VALUES + (1, 100, to_bitmap(100)), + (1, 100, to_bitmap(200)), + (1, 100, to_bitmap(300)), + (1, 300, to_bitmap(300)), + (2, 200, to_bitmap(300)); ``` -```text -+------+------+---------------------------+ -| dt | page | bitmap_to_string(user_id) | -+------+------+---------------------------+ -| 1 | 100 | 100,200,300 | -| 1 | 300 | 300 | -| 2 | 200 | 300 | -+------+------+---------------------------+ +```sql +select bitmap_union_int(dt) from pv_bitmap; ``` +```text ++----------------------+ +| bitmap_union_int(dt) | ++----------------------+ +| 2 | ++----------------------+ ``` -select bitmap_union_int(dt) from pv_bitmap; + +```sql +select bitmap_union_int(dt) from pv_bitmap where dt is null; ``` ```text +----------------------+ | bitmap_union_int(dt) | +----------------------+ -| 2 | +| 0 | +----------------------+ ``` diff --git a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/bitmap-union.md b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/bitmap-union.md index 7c8b6ece79b..383475d780d 100644 --- a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/bitmap-union.md +++ b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/bitmap-union.md @@ -7,7 +7,7 @@ ## 描述 -计算输入 Bitmap 的并集，返回新的 bitmap +计算输入 Bitmap 的并集，返回新的 bitmap。 ## 语法 @@ -19,106 +19,50 @@ BITMAP_UNION(<expr>) | 参数 | 说明 | | -- | -- | -| `<expr>` | 支持 BITMAP 的数据类型 | +| `<expr>` | 支持 Bitmap 的数据类型 | ## 返回值 -返回值的数据类型为 BITMAP。 +返回值的数据类型为 Bitmap。 +当组内没有合法数据时，返回空 Bitmap。 ## 举例 ```sql -select dt,page,bitmap_to_string(user_id) from pv_bitmap; +-- setup +CREATE TABLE pv_bitmap ( + dt INT, + page INT, + user_id BITMAP +) DISTRIBUTED BY HASH(dt) BUCKETS 1 +PROPERTIES ("replication_num" = "1"); +INSERT INTO pv_bitmap VALUES + (1, 100, to_bitmap(100)), + (1, 100, to_bitmap(200)), + (1, 100, to_bitmap(300)), + (2, 200, to_bitmap(300)); ``` -```text -+------+------+---------------------------+ -| dt | page | bitmap_to_string(user_id) | -+------+------+---------------------------+ -| 1 | 100 | 100,200,300 | -| 2 | 200 | 300 | -+------+------+---------------------------+ -``` - -计算 user_id 的去重值： - -``` -select bitmap_count(bitmap_union(user_id)) from pv_bitmap; +```sql +select bitmap_to_string(bitmap_union(user_id)) from pv_bitmap; ``` ```text -+-------------------------------------+ -| bitmap_count(bitmap_union(user_id)) | -+-------------------------------------+ -| 3 | -+-------------------------------------+ -``` - -### Create table - -建表时需要使用聚合模型，数据类型是 bitmap , 聚合函数是 bitmap_union - ++-----------------------------------------+ +| bitmap_to_string(bitmap_union(user_id)) | ++-----------------------------------------+ +| 100,200,300 | ++-----------------------------------------+ ``` -CREATE TABLE `pv_bitmap` ( - `dt` int(11) NULL COMMENT "", - `page` varchar(10) NULL COMMENT "", - `user_id` bitmap BITMAP_UNION NULL COMMENT "" -) ENGINE=OLAP -AGGREGATE KEY(`dt`, `page`) -COMMENT "OLAP" -DISTRIBUTED BY HASH(`dt`) BUCKETS 2; -``` -注：当数据量很大时，最好为高频率的 bitmap_union 查询建立对应的 rollup 表 - -``` -ALTER TABLE pv_bitmap ADD ROLLUP pv (page, user_id); -``` - -### Data Load -`TO_BITMAP(expr)` : 将 0 ~ 18446744073709551615 的 unsigned bigint 转为 bitmap - -`BITMAP_EMPTY()`: 生成空 bitmap 列，用于 insert 或导入的时填充默认值 - -`BITMAP_HASH(expr)`或者`BITMAP_HASH64(expr)`: 将任意类型的列通过 Hash 的方式转为 bitmap - -#### Stream Load - -``` -cat data | curl --location-trusted -u user:passwd -T - -H "columns: dt,page,user_id, user_id=to_bitmap(user_id)" http://host:8410/api/test/testDb/_stream_load -``` - -``` -cat data | curl --location-trusted -u user:passwd -T - -H "columns: dt,page,user_id, user_id=bitmap_hash(user_id)" http://host:8410/api/test/testDb/_stream_load -``` - -``` -cat data | curl --location-trusted -u user:passwd -T - -H "columns: dt,page,user_id, user_id=bitmap_empty()" http://host:8410/api/test/testDb/_stream_load -``` - -#### Insert Into - -id2 的列类型是 bitmap -``` -insert into bitmap_table1 select id, id2 from bitmap_table2; -``` - -id2 的列类型是 bitmap -``` -INSERT INTO bitmap_table1 (id, id2) VALUES (1001, to_bitmap(1000)), (1001, to_bitmap(2000)); -``` - -id2 的列类型是 bitmap -``` -insert into bitmap_table1 select id, bitmap_union(id2) from bitmap_table2 group by id; -``` - -id2 的列类型是 int -``` -insert into bitmap_table1 select id, to_bitmap(id2) from table; +```sql +select bitmap_to_string(bitmap_union(user_id)) from pv_bitmap where user_id is null; ``` -id2 的列类型是 String -``` -insert into bitmap_table1 select id, bitmap_hash(id_string) from table; +```text ++-----------------------------------------+ +| bitmap_to_string(bitmap_union(user_id)) | ++-----------------------------------------+ +| | ++-----------------------------------------+ ``` diff --git a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/topn-array.md b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/topn-array.md index 31a3e5865b6..352a90a69f0 100644 --- a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/topn-array.md +++ b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/topn-array.md @@ -18,16 +18,17 @@ TOPN_ARRAY(<expr>, <top_num> [, <space_expand_rate>]) ## 参数 | 参数 | 说明 | | -- | -- | -| `<expr>` | 要统计的列或表达式 | -| `<top_num>` | 要返回的最高频率值的数量，必须是正整数 | -| `<space_expand_rate>` | 可选项，该值用来设置 Space-Saving 算法中使用的 counter 个数`counter_numbers = top_num * space_expand_rate` space_expand_rate 的值越大，结果越准确，默认值为 50 | +| `<expr>` | 要统计的列或表达式，支持类型为 TinyInt，SmallInt，Integer，BigInt，LargeInt，Float，Double，Decimal，Date，Datetime，IPV4，IPV6，String。 | +| `<top_num>` | 要返回的最高频率值的数量，必须是正整数，支持类型为 Integer。 | +| `<space_expand_rate>` | 可选项，该值用来设置 Space-Saving 算法中使用的 counter 个数`counter_numbers = top_num * space_expand_rate` space_expand_rate 的值越大，结果越准确，默认值为 50，支持类型为 Integer。 | ## 返回值 返回一个数组，包含出现频率最高的 N 个值。 +如果组内没有合法数据，返回 NULL。 ## 举例 ```sql --- 创建示例表 +-- setup CREATE TABLE page_visits ( page_id INT, user_id INT, @@ -36,8 +37,6 @@ CREATE TABLE page_visits ( PROPERTIES ( "replication_num" = "1" ); - --- 插入测试数据 INSERT INTO page_visits VALUES (1, 101, '2024-01-01'), (2, 102, '2024-01-01'), @@ -47,12 +46,15 @@ INSERT INTO page_visits VALUES (2, 105, '2024-01-01'), (1, 106, '2024-01-01'), (4, 107, '2024-01-01'); +``` --- 查找访问量最高的前 3 个页面 +```sql SELECT TOPN_ARRAY(page_id, 3) as top_pages FROM page_visits; ``` +查找访问量最高的前 3 个页面。 + ```text +-----------+ | top_pages | @@ -60,3 +62,17 @@ FROM page_visits; | [1, 2, 4] | +-----------+ ``` + + +```sql +SELECT TOPN_ARRAY(page_id, 3) as top_pages FROM page_visits where page_id is null; +``` + +```text ++-----------+ +| top_pages | ++-----------+ +| NULL | ++-----------+ +``` + diff --git a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/topn-weighted.md b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/topn-weighted.md index 8b9cabdf151..5bc9c104304 100644 --- a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/topn-weighted.md +++ b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/topn-weighted.md @@ -18,18 +18,19 @@ TOPN_WEIGHTED(<expr>, <weight>, <top_num> [, <space_expand_rate>]) ## 参数 | 参数 | 说明 | | -- | -- | -| `<expr>` | 要统计的列或表达式 | -| `<weight>` | 用于调整权重的列或表达式 | -| `<top_num>` | 要返回的最高频率值的数量，必须是正整数 | -| `<space_expand_rate>` | 可选项，该值用来设置 Space-Saving 算法中使用的 counter 个数`counter_numbers = top_num * space_expand_rate` space_expand_rate 的值越大，结果越准确，默认值为 50 | +| `<expr>` | 要统计的列或表达式，支持类型为 TinyInt，SmallInt，Integer，BigInt，LargeInt，Float，Double，Decimal，Date，Datetime，IPV4，IPV6，String。 | +| `<weight>` | 用于调整权重的列或表达式，支持类型为 Double。| +| `<top_num>` | 要返回的最高频率值的数量，必须是正整数，支持类型为 Integer。 | +| `<space_expand_rate>` | 可选项，该值用来设置 Space-Saving 算法中使用的 counter 个数`counter_numbers = top_num * space_expand_rate` space_expand_rate 的值越大，结果越准确，默认值为 50，支持类型为 Integer。 | ## 返回值 -返回一个数组，包含值和对应的加权计数。 +返回一个数组，包含加权计数最高的 N 个值。 +如果组内没有合法数据，返回 NULL。 ## 举例 ```sql --- 创建示例表 +-- setup CREATE TABLE product_sales ( product_id INT, sale_amount DECIMAL(10,2), @@ -38,8 +39,6 @@ CREATE TABLE product_sales ( PROPERTIES ( "replication_num" = "1" ); - --- 插入测试数据 INSERT INTO product_sales VALUES (1, 100.00, '2024-01-01'), (2, 50.00, '2024-01-01'), @@ -49,12 +48,15 @@ INSERT INTO product_sales VALUES (2, 80.00, '2024-01-01'), (1, 120.00, '2024-01-01'), (4, 90.00, '2024-01-01'); +``` --- 查找销售额最高的前 3 个产品（按销售金额加权） +```sql SELECT TOPN_WEIGHTED(product_id, sale_amount, 3) as top_products FROM product_sales; ``` +查找销售额最高的前 3 个产品（按销售金额加权） + ```text +--------------+ | top_products | @@ -62,3 +64,18 @@ FROM product_sales; | [1, 2, 4] | +--------------+ ``` + +```sql +SELECT TOPN_WEIGHTED(product_id, sale_amount, 3) as top_products +FROM product_sales where product_id is null; +``` + +查找销售额最高的前 3 个产品（按销售金额加权） + +```text ++--------------+ +| top_products | ++--------------+ +| NULL | ++--------------+ +``` diff --git a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/topn.md b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/topn.md index a8cc4e8f8de..7aa731ccbd1 100644 --- a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/topn.md +++ b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/aggregate-functions/topn.md @@ -18,16 +18,18 @@ TOPN(<expr>, <top_num> [, <space_expand_rate>]) ## 参数 | 参数 | 说明 | | -- | -- | -| `<expr>` | 要统计的列或表达式 | -| `<top_num>` | 要返回的最高频率值的数量，必须是正整数 | -| `<space_expand_rate>` | 可选项，该值用来设置 Space-Saving 算法中使用的 counter 个数`counter_numbers = top_num * space_expand_rate` space_expand_rate 的值越大，结果越准确，默认值为 50 | +| `<expr>` | 要统计的列或表达式，支持类型为 TinyInt，SmallInt，Integer，BigInt，LargeInt，Float，Double，Decimal，Date，Datetime，IPV4，IPV6，String。 | +| `<top_num>` | 要返回的最高频率值的数量，必须是正整数，支持类型为 Integer。 | +| `<space_expand_rate>` | 可选项，该值用来设置 Space-Saving 算法中使用的 counter 个数`counter_numbers = top_num * space_expand_rate` space_expand_rate 的值越大，结果越准确，默认值为 50，支持类型为 Integer。 | ## 返回值 + 返回一个 JSON 字符串，包含值和对应的出现次数。 +如果组内没有合法数据，返回 NULL。 ## 举例 ```sql --- 创建示例表 +-- setup CREATE TABLE page_visits ( page_id INT, user_id INT, @@ -36,8 +38,6 @@ CREATE TABLE page_visits ( PROPERTIES ( "replication_num" = "1" ); - --- 插入测试数据 INSERT INTO page_visits VALUES (1, 101, '2024-01-01'), (2, 102, '2024-01-01'), @@ -47,16 +47,32 @@ INSERT INTO page_visits VALUES (2, 105, '2024-01-01'), (1, 106, '2024-01-01'), (4, 107, '2024-01-01'); +``` --- 查找访问量最高的前 3 个页面 +```sql SELECT TOPN(page_id, 3) as top_pages FROM page_visits; ``` +查找访问量最高的前 3 个页面。 + ```text +---------------------+ | top_pages | +---------------------+ | {"1":4,"2":2,"4":1} | +---------------------+ +``` + +```sql +SELECT TOPN(page_id, 3) as top_pages +FROM page_visits where page_id is null; +``` + +```text ++-----------+ +| top_pages | ++-----------+ +| NULL | ++-----------+ ``` \ No newline at end of file --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
