--sql 1
select a.* from table_a a left join table_b b on a.id = b.a_id where b.name = 'john';
--sql 2
select * from table_a where id = (select a_id from table_b where name = 'john');

(1) SELECT 
(2)DISTINCT<select_list>
(3) FROM <left_table>
(4) <join_type> JOIN <right_table>
(5)         ON <join_condition>
(6) WHERE <where_condition>
(7) GROUP BY <group_by_list>
(8) WITH {CUBE|ROLLUP
(9) HAVING <having_condition>
(10) ORDER BY <order_by_condition>
(11) LIMIT <limit_number>

(8) SELECT
(9)DISTINCT<select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2)         ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) WITH {CUBE|ROLLUP}
(7) HAVING <having_condition>
(10) ORDER BY <order_by_list>
(11) LIMIT <limit_number>

from_unixtime(bigint unixtime[, string format])
from_unixtime(1250111000,"yyyy-MM-dd") 得到2009-03-12
format可为“yyyy-MM-dd hh:mm:ss”,“yyyy-MM-dd hh”,“yyyy-MM-dd hh:mm”等等

date_format(date/timestamp/string ts, string fmt)
date_format("2016-06-22","MM-dd")=06-22

unix_timestamp(string date)
unix_timestamp('2009-03-20 11:30:01') = 1237573801

to_date(string timestamp)
to_date("1970-01-01 00:00:00") = "1970-01-01"

year(string date)
year("1970-01-01") = 1970
year("1970-01-01 00:00:00") = 1970

month(string date)
month("1970-11-01") = 11
month("1970-11-01 00:00:00") = 11

day(string date)
day("1970-11-01") = 1
day("1970-11-01 00:00:00") = 1


hour(string date)
minute(string date)
econd(string date)

weekofyear(string date)
weekofyear("1970-11-01 00:00:00") = 44
weekofyear("1970-11-01") = 44

datediff(string enddate, string startdate)
datediff('2009-03-01', '2009-02-27') = 2

date_add(string startdate, int days)
date_add('2008-12-31', 1) = '2009-01-01'.

date_sub(string startdate, int days)
date_sub('2008-12-31', 1) = '2008-12-30'
--
CURRENT_DATE() - INTERVAL '1' DAY

current_date()=2021-07-08

current_timestamp=2021-07-08 16:25:54.226

concat(string|binary A, string|binary B...)
concat('foo', 'bar') 返回 'foobar'

SELECT CONCAT_WS(',','First name','Last Name' )  返回  First name, Last Name   

get_json_object(string json_string, string path)
--
对于jsonArray（json数组），如person表的xjson字段有数据：
[{"name":"王二狗","sex":"男","age":"25"},{"name":"李狗嗨","sex":"男","age":"47"}]
--
取出第一个json对象，那么hive sql为：SELECT get_json_object(xjson,"$.[0]") FROM person;
结果是：{"name":"王二狗","sex":"男","age":"25"}
--
取出第一个json的age字段的值：SELECT get_json_object(xjson,"$.[0].age") FROM person;
结果：25

instr(string str, string substr)
select instr("abcde",'b')，结果是2

length(string A)

substr(string|binary A, int start)
substr('foobar', 4) 返回 'bar'

substr(string|binary A, int start, int len)
substr('foobar', 4, 1) 返回 'b' 

if(boolean testCondition, T valueTrue, T valueFalseOrNull)
select IF(1=1,'TRUE','FALSE') 返回 'True'
select IF(1=1, 'working', 'not working') 返回'working'

CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END
CASE 4 WHEN 5  THEN 5 WHEN 4 THEN 4 ELSE 3 END 将返回4

CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END
CASE WHEN  5>0  THEN 5 WHEN 4>0 THEN 4 ELSE 0 END 将返回5
CASE WHEN  5<0  THEN 5 WHEN 4<0 THEN 4 ELSE 0 END 将返回0

cast(expr as <type>)
cast("1" as BIGINT) 将字符串1转换成了BIGINT类型，如果转换失败将返回NULL
event_day  <= CAST('2020-05-01' AS date)

count(*)

count(expr)

count(DISTINCT expr[, expr...])

sum(col)

sum(DISTINCT col)

avg(col)

avg(DISTINCT col)

min(col)

max(col)

xxx() over(PARTITION by yyy ORDER by zzz)

SELECT 
    user_id,
    distinct_id,
    (CASE
        WHEN c < 1000 THEN '1000以下（不含1000元）'
        WHEN c < 2000 THEN '1000~2000（不含2000）'
        WHEN c < 5000 THEN '<2000~5000（不含5000）'
        ELSE '5000元以上（含5000元）'
    END) as "过去7天订单实付金额总和"
FROM (
    SELECT user_id, max(distinct_id) AS distinct_id, sum(order_actual_amount) AS c FROM events
    WHERE date BETWEEN CURRENT_DATE() - INTERVAL '7' day  AND CURRENT_DATE() - INTERVAL '1' day  AND event = 'PayOrder'
    GROUP BY 1
)a

SELECT user_id,distinct_id,DATEDIFF(now(), time) AS "距今天数"
FROM
(SELECT user_id, max(distinct_id) AS distinct_id, MAX(time) AS time
    FROM events
    WHERE date BETWEEN CURRENT_DATE() - INTERVAL '91' day  AND CURRENT_DATE() - INTERVAL '1' day  AND event = 'PayOrder'
    group by 1
) a

SELECT user_id, distinct_id, first_commodity
FROM
  (SELECT user_id, distinct_id, first_commodity, row_number() over(partition by user_id order by cn desc) AS "最受客户自己偏好的商品一级分类"
  FROM
    (SELECT user_id, max(distinct_id) AS distinct_id, first_commodity, COUNT(event) AS cn
      FROM events
      WHERE date BETWEEN CURRENT_DATE() - INTERVAL '91' day  AND CURRENT_DATE() - INTERVAL '1' day  AND event = 'PayOrderDetail' 
      GROUP BY user_id, first_commodity
      having sum(total_price_of_commodity) > 300
    )a1
  )a2
WHERE "最受客户自己偏好的商品一级分类" < 2

SELECT user_id, distinct_id, "登录App次数"
FROM
(
  SELECT user_id, max(distinct_id) AS distinct_id, COUNT(*) AS "登录App次数"
  from events
  WHERE date BETWEEN CURRENT_DATE() - INTERVAL '91' day  AND CURRENT_DATE() - INTERVAL '1' day  AND event = 'LoginButtonClick'
  GROUP BY user_id
) a

SELECT count(*)
FROM events 
WHERE date BETWEEN CURRENT_DATE() - INTERVAL '1' day  AND CURRENT_DATE() - INTERVAL '1' day  AND event = 'AppInstall' AND $is_first_day = 1

SELECT COUNT(1)
from
(
SELECT user_id
FROM events 
WHERE date BETWEEN CURRENT_DATE() - INTERVAL '1' day  AND CURRENT_DATE() - INTERVAL '1' day  AND event = 'AppInstall'
)a
inner join
(
SELECT user_id
FROM events 
WHERE date BETWEEN CURRENT_DATE() - INTERVAL '2' day  AND CURRENT_DATE() - INTERVAL '2' day  AND event = 'AppInstall'
)b on a.user_id = b.user_id

SELECT COUNT(1)
from
(
SELECT user_id
FROM events 
WHERE date BETWEEN '2018-01-01' AND CURRENT_DATE() - INTERVAL '2' day  AND event = 'AppInstall' 
)a
left join
(
SELECT user_id
FROM events 
WHERE date BETWEEN CURRENT_DATE() - INTERVAL '1' day  AND CURRENT_DATE() - INTERVAL '1' day  AND event = 'AppInstall'
)b on a.user_id = b.user_id
WHERE b.user_id is null

SELECT COUNT(1)
from
(
SELECT user_id
FROM events 
WHERE date BETWEEN CURRENT_DATE() - INTERVAL '1' day  AND CURRENT_DATE() - INTERVAL '1' day  AND event = 'AppInstall' AND $is_first_day = 0
)a
left join
(
SELECT user_id
FROM events 
WHERE date BETWEEN CURRENT_DATE() - INTERVAL '2' day  AND CURRENT_DATE() - INTERVAL '2' day  AND event = 'AppInstall'
)b on a.user_id = b.user_id
WHERE b.user_id is null