嵌套JSON查询

-- -------------------------------------------------------------------------------------------------   嵌套JSON筛选demo   start  -- -------------------------------------------------------------------------------------------------
    select
        get_json_object(content, '$.user_id') user_id,
        to_date(
            cast(
            from_unixtime(
                cast(
                get_json_object(content, '$.send_time') as bigint
                ),
                'yyyy-MM-dd'
            ) as string
            )
        ) send_time,
        ds,
        get_json_object(pushTypeInfo, '$.push_key') 
    from 
        (
                        select get_json_object(content, '$.pushTypeInfo') as pushTypeInfo, content ,ds  from 
                    dm_dac_aladdin.ods_push_success
                    where
                        ds in (
                        '2022-05-10',
                        '2022-05-24',
                        '2022-06-30',
                        '2022-07-20',
                        '2022-07-29',
                        '2022-09-09',
                        '2022-11-18',
                        '2022-12-02'
                    )
        )  where get_json_object(pushTypeInfo, '$.projectName') ='安全推送'
 -- -------------------------------------------------------------------------------------------------   嵌套JSON筛选demo   end  -- -------------------------------------------------------------------------------------------------

验证实时下发是否能够提高点击率

首先join下发和点击表，然后算出点击时间-下发时间的 时间跨度；

如果时间跨度集中在0到5之间的最多；

那么说明实时下发是有效的能够实时提醒用户点击；

-- -------------------------------------------------------------------------------------------------   小秘书下发和点击时间验证   start  -- -------------------------------------------------------------------------------------------------
  select
  mtime,
  count(*) from (
    select
      (click.un_click - succ.un_suc) / 60 as m_time,
      CASE
        WHEN (click.un_click - succ.un_suc) / 60 between 0
        and 1 THEN '0'
        WHEN (click.un_click - succ.un_suc) / 60 between 1
        and 5 THEN '1-5'
        WHEN (click.un_click - succ.un_suc) / 60 between 5
        and 30 THEN '5-30'
        WHEN (click.un_click - succ.un_suc) / 60 between 30
        and 120 THEN '30-120' 
        ELSE '大于2小时'
      END as mtime 
    from
      (
        select
         --  count(*)
            unix_timestamp(date_time) as un_suc,
            uid
        from
          dw_bosszp.bg_action
        where
          action = 'xiaomishu-send-success' -- xiaomishu-send-success actionp3
          and actionp3 = '60177584'
          and ds between '2023-07-10'
          and '2023-07-10'
      ) succ
      join (
        select
          unix_timestamp(date_time) as un_click,
          uid
        from
          dw_bosszp.bg_action
        where
          action = 'push-card-click'
          and actionp = '60177584'
          and ds between '2023-07-10'
          and '2023-07-10'
      ) click on succ.uid = click.uid   
  ) a  
    group by
    mtime
 -- -------------------------------------------------------------------------------------------------   小秘书下发和点击时间验证   end  -- -------------------------------------------------------------------------------------------------

insert、update、delete语句中使用select子句

1. insert语句中使用select子句

语法：【没有圆括号】

CREATE TABLE 成绩表
      (学号 char(7),    
       课程号 char(4) ,
       成绩 int )
INSERT INTO 成绩表(学号,课程号,成绩)
SELECT * FROM  sc 
WHERE score>=80
GO
SELECT * FROM 成绩表

注意：

①不要把SELECT子句写在圆括号中。

②INSERT语句中的列名列表应当放在圆括号中，而且不使用VALUES关键字。如果源表与目标表结构完全相同，则可以省略INSERT语句中的列名列表。

③SELECT子句中的列列表必须与INSERT语句中的列列表相匹配。如果没有在INSERT语句中给出列列表，SELECT子句中的列列表必须与目标表中的列相匹配。

2. UPDATE语句中使用SELECT子句

在UPDATE语句中使用SELECT子句，可以将子查询的结果作为修改数据的条件。

SELECT子句要写在圆括号中。

将计算机专业zhangsan选修的“1”号课的成绩改为92分。

方法一：使用SELECT子句
UPDATE sc SET score=92 
WHERE cno='1' AND sno=
                (SELECT sno FROM student
                 WHERE sname= 'zhangsan' AND specialty='计算机')
方法二：使用JOIN内连接
 UPDATE sc 
 SET score=92 
 FROM sc JOIN student ON student.sno=sc.sno
 WHERE cno='1' AND  sname= 'zhangsan' AND  specialty='计算机'

3. DELETE语句中使用SELECT子句

在DELETE语句中使用SELECT子句，可以将子查询的结果作为删除数据的条件。

SELECT子句要写在圆括号中。

将计算机专业zhangsan选修的1号课删除。

方法一：使用SELECT子句
DELETE sc 
WHERE cno='1' AND sno=
                    (SELECT sno FROM student 
           WHERE sname= 'zhangsan' AND specialty='计算机')
方法二：使用JOIN内连接
DELETE sc 
FROM sc JOIN student ON student.sno=sc.sno 
WHERE cno='1' AND sname= 'zhangsan'
              AND specialty='计算机'

SQL查找是否”存在”

推荐写法，SQL不再使用count，而是改用LIMIT 1，让数据库查询时遇到一条就返回，不要再继续查找还有多少条了 业务代码中直接判断是否非空即可，根据查询条件查出来的条数越多，性能提升的越明显，在某些情况下，还可以减少联合索引的创建：

TODO：直接Boolean接收，然后BooleanUtils.isTrue判断

SQL写法

SELECT 1 FROM table WHERE a = 1 AND b = 2 LIMIT 1

Java写法

Integer exist = xxDao.existXxxxByXxx(params);
if ( exist != NULL ) {
  //当存在时，执行这里的代码
} else {
  //当不存在时，执行这里的代码
}

拼接函数

1. concat(string s1, string s2, string s3)

这个函数能够把字符串类型的数据连接起来，连接的某个元素可以是列值。

如 concat( aa, ‘:’, bb) 就相当于把aa列和bb列用冒号连接起来了，aa:bb。

1. cast

用法：cast(value as type)

功能：将某个列的值显示的转化为某个类型

例子：cast(age as string ) 将int类型的数据转化为了String类型

1. concat_ws(seperator, string s1, string s2…)

功能：制定分隔符将多个字符串连接起来，实现“列转行”

例子：常常结合group by与collect_set使用

有表结构a string , b string , c int

数据为

c d 1

c d 2

c d 3

e f 4

e f 5

e f 6

想要得到

c d 1,2,3

e f 4,5,6

语句如下

select a, b, concat_ws(‘,’ , collect_set(cast(c as string)))

from table group by a,b;

1. 上述用的到的 collect_set 函数，有两个作用，第一个是去重，去除group by后的重复元素，

第二个是形成一个集合，将group by后属于同一组的第三列集合起来成为一个集合。与contact_ws

结合使用就是将这些元素以逗号分隔形成字符串。

Supported form(s): ‘CONCAT_WS()’

Mysql的INTERVAL()函数和INTERVAL关键字

一，INTERVAL()函数

INTERVAL()函数可以返回分段后的结果，语法如下：

INTERVAL(N,N1,N2,N3,……….)

其中，N是要判断的数值，N1,N2,N3,…是分段的间隔。

这个函数的返回值是段的位置：

如果N<N1，则返回0，

如果N1<=N<N2，则返回1，

如果N2<=N<N3，则返回2。

所以，区间是前闭后开的。

举个例子：

有这样的数据：

然后执行以下sql: SELECT id,percent,INTERVAL(percent,25,50) from test;

执行结果如下：

意思大概是这样的：percent字段参与判断，设定的区段是25,50，那么小于25的值返回0，大于等于25小于50的值返回1，大于等于50的值返回2。

还可以把INTERVAL()函数用在GROUP BY中：

执行这样的sql：

SELECT

INTERVAL (percent, 0, 26, 51),
COUNT(1)

FROM

test

GROUP BY

INTERVAL (percent, 0, 26, 51);

执行结果如下：

二，INTERVAL关键字

INTERVAL关键字可以用于计算时间间隔，可以有以下用法。

1，直接计算时间间隔。

例1：查询当前时间之前2个小时的日期：

SELECT NOW()-INTERVAL ‘2’ HOUR;

例2：

有这样的表：

执行这样的sql：

SELECT

id,
percent,
t_date,
t_date - INTERVAL 2 HOUR

FROM

test

where t_date - INTERVAL 2 HOUR>’2020-11-02’;

执行结果：

注：INTERVAL后面的数字可以用数字格式或者字符格式，当时间单位是YEAR_MONTH这种时，必须用字符格式。

2，用在时间函数中

INTERVAL关键字可以用在DATE_SUB(),SUBDATE(),ADDDATE()等函数中。

例1：查询两天前的时间：

SELECT NOW(),SUBDATE(NOW(),INTERVAL 2 DAY);

例2：执行这样的sql：

SELECT

id,
percent,
t_date,
DATE_SUB(t_date, INTERVAL 2 HOUR)

FROM

test

WHERE

DATE_SUB(t_date, INTERVAL 2 HOUR) > '2020-11-02';

执行结果：

可用的时间单位：

MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

重点关注一下YEAR_MONTH这种格式的单位，以YEAR_MONTH为例，代表几年又几个月的时间间隔。

比如查询当前时间前一年又三个月的时间，可以这样：

SELECT NOW(),NOW()-INTERVAL ‘1 3’ YEAR_MONTH;

执行结果：

其中：

‘1 3’ YEAR_MONTH的配置就是代表1年3个月，两个数字之间的间隔符用等号，空格，下划线，中划线等等的都可以。

同理，’2 1 3 4’ DAY_SECOND就代表2天1小时3分4秒：

另外，在Oracle中，INTERVAL关键字还有专门的语法，可以起到MySQL中YEAR_MONTH关键字差不多的功能：

INTERVAL ‘integer [- integer]’ {YEAR | MONTH} [(precision)][TO {YEAR | MONTH}]