- Introduction
- Quick Start
- Table Design
- Data Loading
- Overview of data loading
- Load data from a local file system or a streaming data source using HTTP push
- Load data from HDFS or cloud storage
- Routine Load
- Spark Load
- Insert Into
- Change data through loading
- Transform data at loading
- Json Loading
- Synchronize data from MySQL
- Load data by using flink-connector-starrocks
- DataX Writer
- Data Export
- Using StarRocks
- Reference
- SQL Reference
- User Account Management
- Cluster Management
- ADMIN CANCEL REPAIR
- ADMIN CHECK TABLET
- ADMIN REPAIR
- ADMIN SET CONFIG
- ADMIN SET REPLICA STATUS
- ADMIN SHOW CONFIG
- ADMIN SHOW REPLICA DISTRIBUTION
- ADMIN SHOW REPLICA STATUS
- ALTER SYSTEM
- CANCEL DECOMMISSION
- CREATE FILE
- DROP FILE
- INSTALL PLUGIN
- SHOW BACKENDS
- SHOW BROKER
- SHOW FILE
- SHOW FRONTENDS
- SHOW FULL COLUMNS
- SHOW INDEX
- SHOW PLUGINS
- SHOW TABLE STATUS
- UNINSTALL PLUGIN
- DDL
- ALTER DATABASE
- ALTER TABLE
- ALTER VIEW
- BACKUP
- CANCEL BACKUP
- CANCEL RESTORE
- CREATE DATABASE
- CREATE INDEX
- CREATE MATERIALIZED VIEW
- CREATE REPOSITORY
- CREATE RESOURCE
- CREATE TABLE AS SELECT
- CREATE TABLE LIKE
- CREATE TABLE
- CREATE VIEW
- CREATE FUNCTION
- DROP DATABASE
- DROP INDEX
- DROP MATERIALIZED VIEW
- DROP REPOSITORY
- DROP RESOURCE
- DROP TABLE
- DROP VIEW
- DROP FUNCTION
- HLL
- RECOVER
- RESTORE
- SHOW RESOURCES
- SHOW FUNCTION
- TRUNCATE TABLE
- DML
- ALTER ROUTINE LOAD
- BROKER LOAD
- CANCEL LOAD
- DELETE
- EXPORT
- GROUP BY
- INSERT
- PAUSE ROUTINE LOAD
- RESUME ROUTINE LOAD
- ROUTINE LOAD
- SELECT
- SHOW ALTER
- SHOW BACKUP
- SHOW DATA
- SHOW DATABASES
- SHOW DELETE
- SHOW DYNAMIC PARTITION TABLES
- SHOW EXPORT
- SHOW LOAD
- SHOW PARTITIONS
- SHOW PROPERTY
- SHOW REPOSITORIES
- SHOW RESTORE
- SHOW ROUTINE LOAD
- SHOW ROUTINE LOAD TASK
- SHOW SNAPSHOT
- SHOW TABLES
- SHOW TABLET
- SHOW TRANSACTION
- SPARK LOAD
- STOP ROUTINE LOAD
- STREAM LOAD
- Data Types
- Auxiliary Commands
- Function Reference
- Java UDFs
- Window Function
- Date Functions
- convert_tz
- curdate
- current_timestamp
- curtime
- datediff
- date_add
- date_format
- date_sub
- date_trunc
- day
- dayname
- dayofmonth
- dayofweek
- dayofyear
- from_days
- from_unixtime
- hour
- minute
- month
- monthname
- now
- quarter
- second
- str_to_date
- timediff
- timestampadd
- timestampdiff
- to_date
- to_days
- unix_timestamp
- utc_timestamp
- weekofyear
- year
- hours_diff
- minutes_diff
- months_diff
- seconds_diff
- weeks_diff
- years_diff
- Aggregate Functions
- Geographic Functions
- String Functions
- JSON Functions
- Overview of JSON functions and operators
- JSON constructor functions
- JSON query and processing functions
- JSON operators
- Aggregate Functions
- Bitmap Functions
- Array Functions
- cast function
- hash function
- Cryptographic Functions
- Math Functions
- Utility Functions
- System variables
- Error code
- System limits
- SQL Reference
- Administration
- FAQ
- Deploy
- Data Migration
- SQL
- Other FAQs
- Benchmark
- Developers
- Contribute to StarRocks
- Code Style Guides
- Use the debuginfo file for debugging
- Development Environment
- Trace Tools
retention
Description
Checks whether a table contains data that meets specified conditions and returns an array of BOOLEAN values.
Syntax
retention(input)
Parameters
input
: an array that consists of event expressions.
Return value
Returns an array of BOOLEAN values. The array that is returned is of the same length as the array that is specified by the input
parameter.
The value of the first element in the array specified by the
output
parameter isinput[1]
.If both
input[1]
andoutput[
n
]
aretrue
, the value of the nth element in the array specified by theoutput
parameter istrue
.
Examples
Example 1:
mysql> SELECT retention([lo_orderdate = '1997-08-01' AND lo_orderpriority = '2-HIGH',
lo_orderdate = '1997-08-02' AND lo_orderpriority = '1-URGENT', lo_orderdate = '1997-08-03' AND
lo_orderpriority = '5-LOW']) AS r FROM lineorder_flat GROUP BY lo_linenumber;
+---------+
| r |
+---------+
| [1,1,1] |
| [1,1,1] |
| [1,1,1] |
| [1,1,1] |
| [1,1,1] |
| [1,1,1] |
| [1,1,1] |
+---------+
Example 2:
Calculate the proportion of elements that meet the following conditions:
lo_orderdate = '1997-08-02' AND lo_orderpriority = '1-URGENT'
lo_orderdate = '1997-08-03' AND lo_orderpriority = '5-LOW'
mysql> SELECT sum(r[1]), sum(r[2]) / sum(r[1]), sum(r[3]) / sum(r[1]) FROM
(SELECT retention([lo_orderdate = '1997-08-01' AND lo_orderpriority = '2-HIGH',
lo_orderdate = '1997-08-02' AND lo_orderpriority = '1-URGENT', lo_orderdate = '1997-08-03' AND
lo_orderpriority = '5-LOW']) AS r FROM lineorder_flat GROUP BY lo_suppkey) t;
+-------------+---------------------------+---------------------------+
| sum(`r`[1]) | sum(`r`[2]) / sum(`r`[1]) | sum(`r`[3]) / sum(`r`[1]) |
+-------------+---------------------------+---------------------------+
| 43951 | 0.2228163181725103 | 0.2214056562990603 |
+-------------+---------------------------+---------------------------+
Example 3:
Suppose that you have a table named lineorder_flat
and the table consists of the following data:
+--------------+------------------+------------+
| lo_orderdate | lo_orderpriority | lo_custkey |
+--------------+------------------+------------+
| 1022-11-20 | 4-NOT SPECI | 309050 |
| 1222-10-31 | 2-HIGH | 492238 |
| 1380-09-30 | 5-LOW | 123099 |
| 1380-09-30 | 5-LOW | 460237 |
| 1380-09-30 | 5-LOW | 426502 |
| 1022-11-20 | 4-NOT SPECI | 197081 |
| 1380-09-30 | 5-LOW | 918918 |
| 1022-11-20 | 4-NOT SPECI | 327825 |
| 1380-09-30 | 5-LOW | 252542 |
| 1380-09-30 | 5-LOW | 194171 |
+--------------+------------------+------------+
10 rows in set (0.02 sec)
Execute the following statement to call the retention
function:
mysql> SELECT lo_custkey,
retention([lo_orderdate='1022-11-20' AND lo_orderpriority='4-NOT SPECI',
lo_orderdate='1022-11-21' AND lo_orderpriority='4-LONG']) AS retention
FROM lineorder_flat
GROUP BY lo_custkey;
+------------+-----------+
| lo_custkey | retention |
+------------+-----------+
| 327825 | [1,0] |
| 309050 | [1,0] |
| 252542 | [0,0] |
| 123099 | [0,0] |
| 460237 | [0,0] |
| 194171 | [0,0] |
| 197081 | [1,0] |
| 918918 | [0,0] |
| 492238 | [0,0] |
| 426502 | [0,0] |
+------------+-----------+
10 rows in set (0.01 sec)
None of the second bits of the output results is 1
, because no data meets the preceding conditions.
Insert the following data record into the table:
(lo_orderdate='1022-11-21', lo_orderpriority='4-LONG', lo_custkey=460237)
Execute the following statement to call the retention
function:
mysql> SELECT lo_custkey,
retention([lo_orderdate='1022-11-20' AND lo_orderpriority='4-NOT SPECI',
lo_orderdate='1022-11-21' AND lo_orderpriority='4-LONG']) AS retention
FROM lineorder_flat
GROUP BY lo_custkey;
+------------+-----------+
| lo_custkey | retention |
+------------+-----------+
| 327825 | [1,0] |
| 309050 | [1,0] |
| 252542 | [0,0] |
| 123099 | [0,0] |
| 460237 | [0,0] |
| 194171 | [0,0] |
| 197081 | [1,0] |
| 918918 | [0,0] |
| 492238 | [0,0] |
| 426502 | [0,0] |
+------------+-----------+
10 rows in set (0.01 sec)
Still, none of the second bits of the output results is 1
, because no data meets the preceding conditions.
Insert the following data record into the table:
(lo_orderdate='1022-11-21', lo_orderpriority='4-LONG', lo_custkey=327825)
Execute the following statement to call the retention
function:
mysql> SELECT lo_custkey,
retention([lo_orderdate='1022-11-20' AND lo_orderpriority='4-NOT SPECI',
lo_orderdate='1022-11-21' AND lo_orderpriority='4-LONG']) AS retention
FROM lineorder_flat
GROUP BY lo_custkey;
+------------+-----------+
| lo_custkey | retention |
+------------+-----------+
| 327825 | [1,1] |
| 309050 | [1,0] |
| 252542 | [0,0] |
| 123099 | [0,0] |
| 460237 | [0,0] |
| 194171 | [0,0] |
| 197081 | [1,0] |
| 918918 | [0,0] |
| 492238 | [0,0] |
| 426502 | [0,0] |
+------------+-----------+
10 rows in set (0.01 sec)
The second bit of the output result 327825
becomes 1
.