[Check out these tips when you can’t meet some statistical requirements.]

Sum function plus over

Usage: sum(count(1)) over(partition by talent_account_type order by create_date rows between unbounded preceding and current row) sys_total

Description: Group statistical sums according to the talen_account_type dimension, and add them up by monotonically increasing create_date

Group by extension:

In the end, with CUBE can be added to make statistics based on several combinations of dimensions of multidimensional, and all data of a certain dimension can be counted. Typically, NULL fields can be handled with COALESCE (CREATE_DATE,’ all dates ‘) to increase readability.

Dynamically create a table with arbitrary dimensions and date values:

Usage:  select time, talent_type from (select date_add(get_date(-30),a.rk) time,1 talent_type from(select row_number()over(order by 1) as rk from vipdw.dw_vccp_media_ds limit 30) a) union (select date_add(get_date(-30),a.rk) time,2 talent_type from(select row_number()over(order by 1) as rk from vipdw.dw_vccp_media_ds limit 30) a)

From a table you can retrieve 30 entries, dynamically add a date and dimension field, and then just use these two entries.

If null (XXX, 0) is used to create a table with a left join on it. If null (XXX, 0) is used to create a table with a left join on it. If null (XXX, 0) is used to create a table with a left join on it

CONCAT function:

Concat (string A, string B...)

Description: Can be used for string concatenation

Case when

Case when b.id IS NULL then 'else' end as fans_top_is_new

Description: Case when is equal to if.. Else, can be used after a number of keywords or methods, or can be used independently, such as: Count (case when media_essence_col = 3 then 1 end) as media_essence_col

Row_number () function:

Row_number ()over(ORDER BY A.FANS_COUNT DESC)

Description: It can be used to display a rank number according to a rule, such as the above meaning that the rank number is in reverse order according to fans_count