MYSQL 以及oralce 数据库中 如何分组去计数重查询

环境如下：
INSERT INTO cs_table(id, ip, act_number, data) VALUES ('1', '60.174.86.8', '220', '2019-05-05');
INSERT INTO cs_table(id, ip, act_number, data) VALUES ('10', '240e:45b:800:bba7::1', '220', '2019-05-05');
INSERT INTO cs_table(id, ip, act_number, data) VALUES ('11', '240e:45b:800:bba7::1', '2021', '2019-05-05');
INSERT INTO cs_table(id, ip, act_number, data) VALUES ('12', '240e:45b:800:bba7::1', '210414', '2019-05-05');
INSERT INTO cs_table(id, ip, act_number, data) VALUES ('13', '240e:45b:800:bba7::1', '210414', '2019-05-05');
INSERT INTO cs_table(id, ip, act_number, data) VALUES ('14', '240e:45b:800:bba7::1', '1226', '2019-05-06');
INSERT INTO cs_table(id, ip, act_number, data) VALUES ('15', '60.168.18.130', '220', '2019-05-06');
INSERT INTO cs_table(id, ip, act_number, data) VALUES ('16', '60.168.18.130', '2021', '2019-05-06');
INSERT INTO cs_table(id, ip, act_number, data) VALUES ('17', '60.168.18.130', '210414', '2019-05-06');
INSERT INTO cs_table(id, ip, act_number, data) VALUES ('18', '60.168.18.130', '1226', '2019-05-06');
INSERT INTO cs_table(id, ip, act_number, data) VALUES ('19', '60.168.18.130', '220', '2019-05-06');
INSERT INTO cs_table(id, ip, act_number, data) VALUES ('2', '240e:45b:800:bba7::1', '2021', '2019-05-05');
INSERT INTO cs_table(id, ip, act_number, data) VALUES ('20', '60.168.18.130', '2021', '2019-05-06');
INSERT INTO cs_table(id, ip, act_number, data) VALUES ('21', '60.168.18.130', '210414', '2019-05-06');
INSERT INTO cs_table(id, ip, act_number, data) VALUES ('22', '36.5.174.84', '1226', '2019-05-06');
INSERT INTO cs_table(id, ip, act_number, data) VALUES ('23', '36.5.174.84', '220', '2019-05-06');
INSERT INTO cs_table(id, ip, act_number, data) VALUES ('24', '36.5.174.84', '1226', '2019-05-06');
INSERT INTO cs_table(id, ip, act_number, data) VALUES ('25', '36.5.174.84', '220', '2019-05-06');
INSERT INTO cs_table(id, ip, act_number, data) VALUES ('26', '36.5.174.84', '2021', '2019-05-06');
INSERT INTO cs_table(id, ip, act_number, data) VALUES ('27', '240e:45b:800:bba7::1', '210414', '2019-05-06');
INSERT INTO cs_table(id, ip, act_number, data) VALUES ('28', '36.4.78.66', '1226', '2019-05-06');
INSERT INTO cs_table(id, ip, act_number, data) VALUES ('29', '112.27.52.101', '220', '2019-05-06');
INSERT INTO cs_table(id, ip, act_number, data) VALUES ('3', '60.168.18.130', '210414', '2019-05-05');
INSERT INTO cs_table(id, ip, act_number, data) VALUES ('30', '117.65.204.62', '2021', '2019-05-06');
INSERT INTO cs_table(id, ip, act_number, data) VALUES ('4', '36.5.174.84', '1226', '2019-05-05');
INSERT INTO cs_table(id, ip, act_number, data) VALUES ('5', '60.174.86.8', '220', '2019-05-05');
INSERT INTO cs_table(id, ip, act_number, data) VALUES ('6', '60.174.86.8', '2021', '2019-05-05');
INSERT INTO cs_table(id, ip, act_number, data) VALUES ('7', '60.174.86.8', '210414', '2019-05-05');
INSERT INTO cs_table(id, ip, act_number, data) VALUES ('8', '60.174.86.8', '220', '2019-05-05');
INSERT INTO cs_table(id, ip, act_number, data) VALUES ('9', '60.174.86.8', '2021', '2019-05-05');

其中act_number 是活动代号，求每个活动每天的UV是多少，也就是先按日期，再按act_number 分组之后去重
在计数，得到以下结果：

img

请问如何在MYSQL 以及oralce 实现

img

select rs.data, sum(rs.220) as '220', sum(rs.1226) as '1226', sum(rs.2021) as '2021', sum(rs.210414) as '210414'  from (
    select ct.data, count(1) as '220', '' as '1226', '' as '2021', '' as '210414' from cs_table ct where ct.act_number = '220' group by ct.data, ct.act_number
    union all
    select ct.data,  '' as '220', count(1) as '1226', '' as '2021', '' as '210414' from cs_table ct where ct.act_number = '1226' group by ct.data, ct.act_number
    union all
    select ct.data,  '' as '220', '' as '1226', count(1) as '2021', '' as '210414' from cs_table ct where ct.act_number = '2021' group by ct.data, ct.act_number
    union all
    select ct.data,  '' as '220', '' as '1226', '' as '2021', count(1) as '210414' from cs_table ct where ct.act_number = '210414 ' group by ct.data, ct.act_number
) rs group by rs.data


-- 使用分组和oracle行列转换函数pivot

select *
  from (select t2.data, t2.act_number
          from (select ROW_NUMBER() OVER(PARTITION BY t.ip, t.data, t.act_number ORDER BY t.data, t.ip) rn,
                       t.ip,
                       t.data,
                       t.act_number
                  from cs_table t) t2
         where rn = 1)
pivot(count(act_number)
   for act_number in(220 as "220",
                     1226 as "1226",
                     2021 as "2021",
                     210414 as "210414"));

img