浅笑古今的博客

数据表Aid city 1 a 2 b 3 c 4 d 表Bcity name a x1 b x2 c x3 表Cname amount x1 100 x2 200 WITH tmp1 AS (SELECT 1 AS id, 'a' AS cityUNION ALLSELECT 2 AS id, 'b' AS cityUNION ALLSE...

start_point_lng 为起点 longitude （经度）start_point_lat 为起点 latitude（纬度）end_point_lng 为起点 longitude （经度） end_point_lat 为终点 latitude（纬度）6378137*2*ASIN(SQRT(POWER(SIN((ta.start_point_lat-ta.end_point_lat)*ACOS(-1)/360),2) +COS(ta.start_point_lat*ACOS(-1)/1..

数据样式，user apps X123 ["123,微信,abc","234,QQ,bcd"] 抽取apps字段中的“微信”和“QQ”，目标数据如下，user apps X123 QQ,微信 思路：按","将apps字段进行拆分成行，然后在按，进行分割后取第二部分，select user,concat_ws(',', collect_set(split(app, '\\,')[1])) AS applist --从0开始计数，第二部分标号为1fro

示例，name label 张三 A 张三 B 张三 C hive逻辑，select name ,CONCAT_WS(',', SORT_ARRAY(COLLECT_SET(label))) as label_arrfrom table_name group by name 结果如下，name label_arr 张三 A,B,C

需求：part为地区，amount为消费金额，按amount降序排列并对amount累计求和。造一批数据，SELECT '1' AS part, 11 AS amountUNION ALLSELECT '2' AS part, 33 AS amountUNION ALLSELECT '3' AS part, 22 AS amountUNION ALLSELECT '4' AS part, 55 AS amountUNION ALLSELECT '5' AS part, 44 AS

字段类型如下，字段名 类型 id String orderinfo String 数据示例如下，id orderinfo 1 [{"Name":"王朝","orderDate":"2020-08-14"},{"Name":"马汉","orderDate":"2020-08-14"}] 2 [{"Name":"张龙","orderDate":"2020-08-14"},{"Name":"赵虎","orderDate":"2020-08-14"}]

针对员工最大连续打卡天数等类似问题SQL求解，思路如下，示例数据如下，id date success 1 2020/4/2 1 1 2020/4/3 1 1 2020/4/4 1 1 2020/4/5 0 1 2020/4/6 1 1 2020/4/7 1 2 2020/4...