1.1 Basic usage #

select event, event_time
from events
where event_date >= '2020-08-10'
and event_date <= '2020-08-18'
and event_time > 1597161600
limit 20;
The front-end displays 1000 items, and the download can download 1000000 items.

1.2 Field Description #

1.2.1 events table #

The query range of this table is: user SSID/UUID, event, event properties, event user properties, all_value type of user properties and business object properties.

• stat_standard_id
  • Statistical caliber ID, SSID or UUID, depending on the statistical caliber of the app
• user_id
  • User ID, empty string when anonymous
• hash_uid
  • When joining with the users table, it is recommended to use hash_uid to improve join efficiency and speed up queries
• device_id
  • Device ID
• web_id
• event
  • event name
• event_date
  • Event date, any SQL is recommended to specify the event time, otherwise derive according to event_time
• event_time
  • Event occurrence timestamp, 10 digits
  • event_date is automatically derived if and only if event_time is used as a constraint; therefore time and server_time are not recommended
• time
  • Event timstamp, 10 digits in history, 13 digits now
• server_time
  • Server level received the timestamp of the event, 10 bits
• content
  • An overview of the behavior log, including basic information about events, event properties, and more
• event_params.xxx.yyy
  • Event property, the format is event_params. Event name. Event property name , at this time SQL will only query the data related to the event.
  • The event name can be omitted and written as event_params. Event property name . At this time, SQL will query all events.
• user_profiles.xxx
  • User property in the format user_profiles. User property name
• item_profiles.xxx.yyyy
  • Business object properties in the format item_profiles. Business object name. Business object property name
• Map column
  • Contains string_params, int_params, float_params, string_array_params, etc., which correspond to the location after the event attributes of string (version), int (datetime), float, and list are dropped into the library. For example, the event property a of the list type can be obtained by mapElemet (string_array_params, 'a'), and the result is consistent with event_params.
  • Typical scenario of using map column: reported some event attributes that do not conform to the SQL specification, such as'wechat.uid ', there are points in this attribute, if event_params.' wechat.uid ' (for attribute names that do not conform to the SQL specification, custom queries will automatically add single quotes as identification), may cause query errors, but use mapElemet (string_params,' wechat.uid ') to query normally.
  • The disadvantages of using map columns: 1. The SQL editor needs to perceive the type of the event attribute; 2. If the attribute type changes, it is difficult for the SQL editor to grasp which map column should be queried. At this point, you can check the content column first to determine what map column the corresponding attribute is in.
• Other fields

Tip: event_params.xxx .yyy, user_profiles.xxx, item_profiles.xxx .yyy three fields suggest aliasing, otherwise the query may have problems.

1.2.2 Users table #

The query range of this table is: user SSID/UUID, all_value/last_value type of user properties.

• stat_standard_id
  • Statistical caliber ID, with the same meaning as the events table
• user_id
  • user ID
• ssid
  • ssid
• device_id
• web_id
• last_active_date
  • Represents the last active time of the user, which can be understood as the last refresh time of the record in the users table. Currently supports "YYYY-MM-dd" and "YYYMMdd" formats, for example: last_active_date = '2020-08-10'.
  • When the events table and the users table are joined, the last_active_date is automatically derived according to the event_date limit of the events table; for example, if the query event is 2021-08-01, then the last_active_date will be derived to be greater than or equal to 2021-08-01 (because if the last_active_date is less than 2021-08-01, then the user must not have any events after 2021-08-01).
• user_profiles.xxxx
  • User property in the format user_profiles. User property name
  • all_value and last_value, but the values stored in the users table are the latest values; to query the historical values of user properties of all_value types, you can query in the events table.
• string_profiles，int_profiles，float_profiles， string_array_profiles。
  • Map columns, similar to the transaction table.
• Other fields

1.2.3 Items table #

The query range of this table is: all_value/last_value type of business object related information

• item_name
  • business object name
• last_active_date
  • Represents the last refresh time of the business object property.
• hash_item_id
  • Used for joining transaction tables
• item_profiles.xxx.yyyy
  • Business object properties in the format item_profiles. Business object name. Business object property name
• string_profiles，int_profiles， float_profiles，string_array_profiles。
  • Map columns, similar to the transaction table.
• Other fields

1.2.4 Cohorts table #

The query range of this table is: the user statistics contained in the sub-group caliber id, hash_uid, sub-group id, etc

• stat_standard_id

  • Statistical caliber id

• hash_uid

  • The statistical aperture id is hashed, which is usually used for join and in subqueries, and the query speed is faster than stat_standard_id.

• cohort_id

  • grouping id

• Under the "metadata" tab, you can view all subgroup names, subgroup IDs, and subgroup numbers.

• Currently does not support querying the historical version of the cluster, so the current query through the cluster id is the most recent successfully refreshed cluster, consistent with other advanced analysis scenarios.
  

1.3 Query range #

• Time frame: nearly a year (including today)
• Data scope: events, event properties, user properties, and business object properties.

1.4 Date filtering #

For event_date and last_active_date, the following functions are currently supported. If you need other functions, you can give feedback to customer service.
Note: Current custom SQL queries do not support SQL queries using dynamic parameters, such as:

select event, event_time from events where event = ‘app_launch’ and event_date = ${event_date} limit 20;
--Where event = ${event_name} This dynamic parameter is passed in by subsequent dynamic, SQL queries in the form of such dynamic parameters are currently not supported

Support function:
subtractMonths,subtractWeeks,subtractDays
addMonths, addWeeks, addDays
toStartOfQuarter,toStartOfMonth,toStartOfISOYear,toMonday,toStartOfWeek
Note: The toStartOfWeek function, when the mode is even, starts on Monday, otherwise it is Sunday.
Wrong usage:

select event from events where toMonth(event_date) = 9；

select event from events where subtractDays(event_date, 10) = '2021-09-20'；

In the above way, the custom query will prompt that the event_date cannot be derived.
Correct usage:

select event from events where event_date >= subtractDays(today(), 10)；

1.4.1 Events table #

In any case, it is strongly recommended to use event_date as the date filter, and for more precise time intervals, use event_date + event_time to limit.

• event_date

event_date indicates the date of the event, accurate to the day, can be used to speed up the query, the default default is the last 7 days (including today).
Currently only the format "YYYY-MM-dd" is supported, for example: event_date = '2020-08-10'.

• event_time

event_time represents the time of the event, a 10-digit timestamp, accurate to the second, and can be used to provide a more accurate time interval.

• Time Zone

The event_date of the domestic station access application is UTC + 8 (East Eighth District) time, and the event_date of the international station access application is UTC (Zero Time Zone) time.
For example:
event_time = 1597273200, domestic station event_date '2020-08-13'
event_time = 1597273200, international station event_date '2020-08-12'
Domestic station access application: Check the events that occurred from 6:00 on August 10, 2020 to 14:00 on August 12, 2020 Beijing time

select event
from events
where
event_time >= 1597010400
and event_time <= 1597212000
and event_date >= '2020-08-10'
and event_date <= '2020-08-12'

The international station accesses the application, and the time zone of the application is UTC time: check the events that occurred from 6:00 UTC on August 10, 2020 to 14:00 UTC on August 12, 2020

select event
from events
where
event_time >= 1597039200
and event_time <= 1597240800
and event_date >= '2020-08-10'
and event_date <= '2020-08-12'

The international station accesses the application, and the time zone of the application is UTC + 7 time: Query the events that occurred from 6:00 UTC + 7 time on August 10, 2020 to 14:00 UTC + 7 time on August 12, 2020, which is equivalent to querying the events that occurred from 23:00 UTC time on August 9, 2020 to 7:00 UTC time on August 12, 2020:

select event
from events
where
event_time >= 1597014000
and event_time <= 1597215600
and event_date >= '2020-08-09'
and event_date <= '2020-08-12'

Note : Whenever possible, it is recommended that you specify a start and stop time for event_date in SQL to speed up queries. If you don't add it, the SQL custom query feature automatically assembles time limits, which may produce data that is different from the time period you expected.

1.4.2 Users, items table #

When joining with the events table, last_active_date can be automatically derived from event_date, otherwise it is recommended that the user specify last_active_date range.
Semantic example:

1. Check the gender of active users after August 10, 2020.

select user_profiles.gender from users where last_active_date >= '2020-08-10'

2. Check the gender of users who have done A events before August 10, 2020

select gender, hash_uid, user_id
    from events as table_a
            left join
            (   
                select hash_uid, user_profiles.gender as gender from users
                where user_profiles.gender is not null
            ) as table_b 
    on table_a.hash_uid == table_b.hash_uid
   where event_date <= '2020-08-10' and event = 'A'
    limit 500;

The SQL does not specify a start event_date for the event, so the default start event_date is 365 days ago today, assumed to be '2019-10-11'. At this point, in the join clause, the default query last_active_date > = '2019-10-11' users.
It is worth noting that the typical misspelling of this case is:

select gender, hash_uid, user_id
    from events as table_a
            left join
            (   
                select hash_uid, user_profiles.gender as gender from users
                where user_profiles.gender is not null where last_active_date <= '2020-08-10'
            ) as table_b 
    on table_a.hash_uid == table_b.hash_uid
   where event_date <= '2020-08-10'
    limit 500;

If a user enters last_active_date < = '2020-08-10', the semantics of the join clause becomes - querying for users who had no events after August 10, 2020, rather than querying for users who had events before August 10, 2020.

1.5 Common functions #

1. Approximate percentiles, quantiles, and approximate median

quantile(level)(expr)
parameter

• Level - percentiles. Optional parameter, the recommended value range of level is [0.01, 0.99], and the default value is level = 0.5, which is the calculated median.
• Expr - expression. Optional numeric, date, or time data types

Median (expr) is equivalent to quantile (0.5) (expr)
Note: This function uses Reservoir_sampling stochastic algorithm, so the results are approximate and non-deterministic.
For example, in the order event of August 10, 2020, the percentiles are 0.2.
select quantile(0.2)(event_params.order.money) from events where event = 'order' and event_date = '2020-08-10' limit 1;

2. Variance function varSamp

Calculate sigma ((x - x) ^ 2)/(n - 1) , where n is the sample size and x is the average of x .
Example: Query the variance of the amount in the order event on August 10, 2020
select varSamp(event_params.order.money) from events where event = 'order' and event_date = '2020-08-10' limit 1;

3. conditional function if

Grammar:
SELECT if(cond, then, else)
If the condition cond evaluates to a non-zero value, the result of the expression then is returned, and the result of the expression else, if present, is skipped. If cond is zero or NULL, the result of the then expression is skipped and the result of the else expression, if present, is returned. 、
Parameter:

• Cond: conditional expression
• Then: The expression returned when the condition is satisfied
• Else: The expression returned when the condition is not met

Example: Query the person who triggered event A more than event B between October 6 and 12, 2020.
select distinct user_unique_id
from (select user_unique_id as user_unique_id,
if(event = 'A', count(1), 0) as event1_cnt,
if(event = 'B', count(1), 0) as event2_cnt
from events
where event in ('A', 'B')
and event_date >= '2020-10-06'
and event_date <= '2020-10-12'
group by user_unique_id, event
limit 1000)
group by user_unique_id
having sum(event1_cnt) > sum(event2_cnt)

4. Array function arrayEnumerate (arr)

Returns an array of the same size as the source array, where each element represents the number of times the original array element corresponding to its subscript appears in the original array. Common usage is similar to the windowing function in hive row_number ()
Parameter:

• arr array

Example: Query the previous three times of different events from October 25 to November 1, 2020
select *
from (
select event, time_arr, row_number
from (
select event,
groupArray(time) as time_arr,
arrayEnumerate(time_arr) as row_number
from (select distinct event, event_time as time
from events
where event_date >= '2020-10-25'
and event_date <= '2020-11-01'
order by time) a
group by event
)
array join
time_arr
, row_number
)
where row_number <= 3;
Result set:

Supplement:
This usage is similar to row_number () for HQL, which is written as:
select *
from
(
select event,
row_number() over(partition by event order by event_time) as row_number
from events
) a
where a.row_number <= 3;
Similar functions:
arrayEnumerateUniq、arrayEnumerateDense

5. match、multiMatchAny、multiMatchAnyIndex

match(haystack, pattern)

• Hystack requires matching fields
• Pattern A regular expression that conforms to the RE2 syntax re2: https://github.com/google/re2/wiki/Syntax
• A return value of 0 indicates no match, and 1 indicates a match.

Example:

select distinct user_profiles.user_id, multiMatchAny(user_profiles.user_id, ['^[0-9]*$', '^\d{10}$']) as is_match from events where event_date > '2021-03-01' limit 20;

Result set:

multiMatchAny(haystack, [pattern1, pattern2, ...])

• [Pattern1, pattern2,...] Any regular expression that conforms to the PCRE syntax.
• A return value of 0 indicates all matches, and 1 indicates at least one pattern match.
• This function matches faster when matching substrings.
• The haystack string needs to be less than 2 ^ 32 bytes.

multiMatchAnyIndex(haystack, [pattern1, pattern2, ...])

• Similar to multiMatchAny
• The return value is the index that matches haystack, the index starts at 1, and no match returns 0.

Example:

select distinct user_profiles.user_id, multiMatchAnyIndex(user_profiles.user_id, ['^[0-9]*$', '^([a-zA-Z0-9_-]\.)+@[a-zA-Z0-9_-]+(\.[a-zA-Z0-9_-]+)+$', '(\.[a-zA-Z0-9_-]@)+']) as regex_index from events where event_date > '2021-03-01' limit 2;

Result set:

1.6 Common usage examples #

Query user behavior on a given day based on uuid #

select event
from events
where user_profiles.user_id = 'datafinder@datarangers.com'
and event_date = '2020-08-10'
limit 20

Query the total number of occurrences of a certain event for users in a certain time period #

select count(event)
from events
where
event = 'event_name'
and event_time > 1596988800
and event_time < 1597161600
limit 20

Query the number of users who have experienced behavior 1 and behavior 2, but not behavior 3 #

select count(distinct hash_uid)
from events
where
(event = 'event_1' or event = 'event_2' and event != 'event_3')
and event_time > 1596988800
and event_time < 1597161600
limit 20

Query the user's first-time behavior attributes #

select user_profiles.user_id, event_params.{event_name}.{event_param_name} from events
where event = 'sogo'
and event_date > '2020-01-01'
order by event_time desc
limit 10;

Query the distribution of user purchases over a period of time #

select
case
when amount > 10 then 'x>10'
when amount >5 and amount <= 10 then '5<x<=10'
else 'x<=5'
end
from
(
select event_params.buy.amount as amount
from events
where event = '`buy`'
and event_date > '2020-06-01' and event_date < '2020-08-02'
limit 200
)

When querying for event-related information, use grouping as a condition #

select event from events where hash_uid in (select hash_uid from cohorts where cohort_id = 154) and event_date = yesterday()

Query the user_id of users in the cluster #

select user_id from users where hash_uid in (select hash_uid from cohorts where cohort_id = 154) and last_active_date >= '2021-01-01'

Complex queries using the WITH clause #

Refactor the query using the WITH clause
WITH filtered_valid_users AS (
    -- Sub-query: filter out user_id non-empty user base information
    SELECT 
        hash_uid, 
        user_id 
    FROM users 
    WHERE user_id IS NOT NULL
)
Main query: associated transaction table and filtered user table, filtered by date
SELECT 
    `event_params.predefine_pageview_hide.duration` AS pageview_hide_duration,  Alias fields for easy identification
    `event_params.duration` AS event_duration,
    event_time,
    table_a.hash_uid,
    filtered_valid_users.user_id
FROM events AS table_a
INNER JOIN filtered_valid_users 
    ON table_a.hash_uid = filtered_valid_users.hash_uid  SQL standard equality operator
WHERE 
    event_date BETWEEN '2026-03-13' AND '2026-03-15'  -- Simplified date range judgment
LIMIT 500;

For details on how to use the function: https://clickhouse.com/docs/zh/sql-reference/functions/

5.1 How to query user attributes? #

Query the country ID of active users after August 1, 2020:

select user_profiles.loc_country_id from users
where last_active_date > '2020-08-01'
limit 200;

The format of the user property column names is user_profiles. {user_profile_name}.

5.2 How to query event properties? #

The format of the event property column names is event_params. {event_name}. {event_param_name}. An example is as follows:

select user_profiles.os_name, event_params.app_launch.session_duration, network_type, device_brand, event_time
from events
and event_date >= '20200812' and event_date <= '20200819'
limit 20;

Note: Although the WHERE condition in the sample SQL does not contain event = 'app_launch', according to
event_params app_launch session_duration field semantics will still only query session_duration attribute values under app_launch events.
If you need to query the attribute value of the same attribute name under different events, please contact customer service.

5.3 How to query the relationship between events and event properties? #

It can be viewed in DataRangers' "Data Management" feature.
Search for "event_params" in the data table to see how all events correspond to event properties.

5.4 How to query business object properties? #

The business object property column name format is item_profiles. {item_name}. {item_param_name}. An example is as follows:

select item_profiles.book.name, item_profiles.book.authors, item_profiles.phone.price from items limit 30;

The query business object is the name and author of the book, and the query business object is the price of the phone. Similar to the query event properties, the sample SQL does not specify item_name in ('book', 'phone'), but the custom query function automatically derives item_name based on the semantics of fields such as item_profiles.book. Authors, so the sample SQL is equivalent to the following SQL:

select item_profiles.book.name, item_profiles.book.authors, item_profiles.phone.price from items where item_name in ('book', 'phone') limit 30;

5.5 How can I inquire about the relationship between events and users? #

JOINs of transaction table and user table can be done through hash_uid. An example is as follows:

select gender, hash_uid, user_id
    from events as table_a
            left join
            (   
                select hash_uid, user_profiles.gender as gender from users
                where user_profiles.gender is not null
            ) as table_b 
    on table_a.hash_uid == table_b.hash_uid
   where event_date <= '2020-08-10' and event = 'A'
    limit 500;

Note: It is recommended to use hash_uid for JOIN, JOIN through other fields may cause the query to time out.

5.6 How to query the relationship between events and business objects? #

You can do a JOIN query by event_params the xxx_hash_item_id of the corresponding event and the hash_item_id of the corresponding business object in the items table. An example is as follows:

select event_params.buy.book_hash_item_id as book_item_id, 
       event_params.buy.browser as buy_browser,
       item_table.book_name,
from events event_table
any inner join 
    (select hash_item_id, item_profiles.book.name as book_name,
    from items 
    where item_name = 'book') item_table 
on arrayJoin(if(isNotNull(event_params.buy.book_hash_item_id), assumeNotNull(event_params.buy.book_hash_item_id), [NULL])) 
   = item_table.hash_item_id
where event_date > '2020-12-10'
limit 20;

The role of the above example is to query the value of the browser and the name of the book when the business object attribute associated with the buy event is book.
Note:

1. JOIN is used

arrayJoin(if(isNotNull(event_params.buy.book_hash_item_id), assumeNotNull(event_params.buy.book_hash_item_id), [NULL]))

Converts book_hash_item_id of Array type to scalar for JOIN convenience.

2. event_params, user_profiles, item_profiles prefixed variables do not conform to the SQL specification, is a custom query function to simplify field semantics, reduce the query threshold and abstract the field, so in JOIN, sub-query and other multi-table query, it is strongly recommended to take an alias for the above field.

5.7 How to query the $is_first_day attribute in event analysis? #

The $is_first_day attribute is a virtual event attribute, and the Finder query side compares the $is_first_day attribute value according to the user event occurrence time and the user's first time occurrence time. If you need to query, you can refer to the following SQL:

1. Query the $is_first_day attribute value of the browse event.

select (toStartOfDay(toDateTime(event_time, 'Asia/Shanghai')) ==
       toStartOfDay(toDateTime(ut.first_event_time, 'Asia/Shanghai'))) as `$is_first_day`
from events et global any
         inner join (
    select hash_uid, user_profiles.first_event_time as first_event_time from users
    ) ut on et.hash_uid == ut.hash_uid
where event = 'browse'
  and event_date >= '2020-12-22'
  and event_date <= '2020-12-28';

2. Check the number of browsing events and the corresponding number of days on the first day from December 22 to 12 and 28.

select toUInt32((event_time - toUnixTimestamp('2020-12-22', 'Asia/Shanghai')) / 86400) as t, count(1) as cnt
from events et global any
         inner join (
    select hash_uid, user_profiles.first_event_time as first_event_time from users
    ) ut on et.hash_uid == ut.hash_uid
where event = 'browse'
  and event_date >= '2020-12-22'
  and event_date <= '2020-12-28'
  and (toStartOfDay(toDateTime(event_time, 'Asia/Shanghai')) ==
      toStartOfDay(toDateTime(ut.first_event_time, 'Asia/Shanghai')))
group by t
order by t ;

5.8 Why is the behavior of the same uuid inconsistent in the behavior scrutiny and in the custom query? #

Because during the behavioral scrutiny, the uuid will be converted into an ssid query, so both real-name and anonymous behaviors can be found. However, in a custom query, if user_id = xxx is given in SQL, only real-name behavior will be queried, not anonymous behavior.

5.9 Why are some properties available in event analysis, but not in custom queries? #

Custom queries currently do not support "old and new users", virtual properties, virtual events, and circled events.
Alternatives are:

• New and existing users: see 5.4 How to condition new and existing users.
• Virtual properties: SQL that can directly use virtual properties.
• Virtual events: Query using the same conditions as virtual events.
• Circle events: there is no alternative.
  If you really want to use the above data directly in your custom queries, you can let us know through a ticket or a customer success manager.

5.10 How to query event public properties? #

Event public properties usually appear as follows:

event_params.any_event.公共属性名
event_params.any_active_event.公共属性名
event_params.事件名.公共属性名
event_params.公共属性名

When querying the following SQL, the scope of the query is all events.

select event_params.any_event.common_param_a from events;
或者
select event_params.common_param_a from events;

When querying the following SQL, the scope of the query is all active events.

select event_params.any_active_event.common_param_a from events;

When querying the following SQL, the scope of the query is event_1 events.

select event_params.event_1.common_param_a from events;

The above SQL is equivalent to -

select event_params.any_event.common_param_a from events where event = 'event_1';

If a SQL contains event_params any_event.xxx, event_params any_active_event.xxx, event_params. Specific event name.xxx, then the event restriction rules for the query are as follows:

The relationship between rules is or, for example:

5.11 Query the property value of the same property name under different events #

Use the event_param event property name so that custom queries do not automatically assemble event limits, requiring you to specify the event in the WHERE condition, such as:

select event_params.url as url from events where event in ('event_1', 'event_2') and event_date = today();

Note: If there are many events containing this property, it may result in an increase in SQL parsing time.

5.12 Whether custom SQL supports dynamic parameters #

Current custom SQL queries do not support SQL queries using dynamic parameters, such as:

select event, event_time from events where event = ‘app_launch’ and event_date = ${event_date} limit 20;
--Where event = ${event_name} This dynamic parameter is passed in by subsequent dynamic, SQL queries in the form of such dynamic parameters are currently not supported