[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