同学遇到的一个面试题,hivesql没有接触过,用sql实现一下
其实一开始没有看懂“a”这列代表什么意思,后来想明白,按照shop_id分组num求和之后,item_id在总和的百分比。
而“b”代表的就是 num在每个分组的排序,并且从小到大。
mysql 5.7版本
创建数据表
CREATE TABLE `test02` (
`shop_id` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`item_id` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`num` int(11) DEFAULT NULL
) ;
INSERT INTO `test02` VALUES ('A', 'a', 10);
INSERT INTO `test02` VALUES ('A', 'b', 12);
INSERT INTO `test02` VALUES ('B', 'a', 8);
INSERT INTO `test02` VALUES ('A', 'c', 5);
INSERT INTO `test02` VALUES ('B', 'c', 8);
INSERT INTO `test02` VALUES ('C', 'b', 9);
然后实现第一步
求a列
SELECT
t.shop_id,
t.item_id,
t.num,
TRUNCATE (num / c.s, 2) a
FROM
test02 t
LEFT JOIN (
SELECT
sum(num) s,
shop_id sc
FROM
test02
GROUP BY
shop_id
) AS c ON t.shop_id = c.sc
执行结果:
然后求b列,b列值相当于按照shop_id 进行分组,num在组里的大小排序,此处倒序
例如,按照A进行分组,item_id的num=12 最大,所以它对应的b为1,其次a为2,c为3,B组中,a和c的num值相同,所以都为1
所以我们声明三个个变量,一个是 @i ,@s_shop_id,@s_num
当s_shop_id等于当前的shop_id,并且@s_num大于当前的num,那么@i 加一,否则就等于1。
SET @i=1;
SELECT
t.shop_id,
t.item_id,
t.num,
TRUNCATE (num / c.s, 2) a,
(
@i := CASE
WHEN @s_shop_id = t.shop_id
AND @s_num > t.num THEN
@i + 1
ELSE
1
END
) b,
@s_shop_id := t.shop_id ,@s_num := t.num ,@s_shop_id ,@s_num
FROM
test02 t
LEFT JOIN (
SELECT
sum(num) s,
shop_id sc
FROM
test02
GROUP BY
shop_id
) AS c ON t.shop_id = c.sc
ORDER BY
t.shop_id,
t.num DESC
但是同样的sql,执行第一遍和第二遍的效果完全不一样。
执行第一遍结果:
执行第二遍结果
往后每次执行效果都与第二遍执行结果一致,但是第一次执行就不一样,我也不知道为什么,希望有大神来指导。
这样就求出啦a列和b列。
不好意思,刚才我感觉这个数据漏洞,例如B组数据,a和c值并列第一可以,如果并列第二呢?于是我把最后一条数据的shop_id值改成了B。数据如下:
按照逻辑,那么B组的最大值为item_id=b,num=9,那么B组的 a c,就并列第二
按照上边的sql执行结果如下:
按照逻辑,B组中 b最大,a和c并列,a和c 在b列值都应该为1,所以重新修改sql
SELECT
t.shop_id,
t.item_id,
t.num,
TRUNCATE (num / c.s, 2) a,
(
@i := CASE
WHEN @s_shop_id = t.shop_id THEN
CASE
WHEN @s_num > t.num THEN
@i + 1
ELSE
@i
END
ELSE
1
END
) b,
@s_shop_id := t.shop_id ,@s_num := t.num ,@s_shop_id ,@s_num
FROM
test02 t
LEFT JOIN (
SELECT
sum(num) s,
shop_id sc
FROM
test02
GROUP BY
shop_id
) AS c ON t.shop_id = c.sc
ORDER BY
t.shop_id,
t.num DESC
这样执行效果如图:
数据改回原先的,也没有问题,大功告成,只是第一遍执行效果,和之后的执行效果依旧不一样。不知道为什么。