mysql默认结束标识符为";",但是有多个语句需要执行,所以定义$$为一句sql的结束标志,取消";"的所代表的意义;
delimiter $$
如果存在名字为recyclingStatistical的procedure(存储过程)则删除;
DROP PROCEDURE IF EXISTS bookStatistical;
创建(创建函数使用的关键字为function 函数名());
CREATE PROCEDURE bookStatistical()
BEGIN
# 声明变量
DECLARE b_day varchar(10);
DECLARE b_id int;
# 设置游标初始值为0;
DECLARE flag INT DEFAULT 0;
# DECLARE(定义变量) CURSOR(游标=数组中的下标,标识数据去到什么地方了) 记录sql查询的结果;
DECLARE id_list CURSOR FOR SELECT LEFT(click_time,10),book_id FROM t_book_click WHERE click_time < ‘2019-12-04 00:00:00’ GROUP BY LEFT(click_time,10),book_id ORDER BY LEFT(click_time,10),book_id;
# 为下面while循环建立一个退出标志,当游标遍历完后将flag的值设置为1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1;
# 打开定义的游标id_list;
OPEN id_list;
# 将游标中的值赋给定义好的变量,实现for循环的要点;
FETCH id_list INTO b_day,b_id;
WHILE flag <> 1 DO
# 在T-SQL中,局部变量必须以@作为前缀,声明方式set,select还有点差别;
SET @book_name = '';
SET @book_gender = -1;#男女频(0 男 1女)
SET @order_num = 0;#订阅数
SET @chapter_num = 0;#付费章节数
SET @order_book_num = 0;#订阅人数
SET @book_clicknum = 0;#真实点击数
SET @click_num = 0;#真实点击人数
SET @order_all_num = 0.00;#总均订 = 订阅数 / 付费章节数
SET @order_per_capita_num = 0.00;#人均订 = 订阅数 / 订阅人数
SET @orderRate = 0.00;#转订率 = 订阅人数 / 真实点击人数
IF (SELECT COUNT(*) FROM t_book WHERE book_id = b_id) > 0 THEN
SET @book_name = (SELECT book_name_cn FROM t_book WHERE book_id = b_id);
SET @book_gender = (SELECT book_gender FROM t_book WHERE book_id = b_id);
END IF;
# 根据游标中存储的日期和书籍id查询出该书在存储日期内的订阅数,更新到声明的变量中;
IF (SELECT COUNT(*) FROM t_member_consume WHERE book_id = b_id AND LEFT(consume_time,10) = b_day) > 0 THEN
SET @order_num = (SELECT COUNT(*) FROM t_member_consume WHERE book_id = b_id AND LEFT(consume_time,10) = b_day);
END IF;
#付费章节数
IF (SELECT COUNT(*) FROM t_book_chapter WHERE book_id = b_id AND chapter_ispay = 1) > 0 THEN
SET @chapter_num = (SELECT COUNT(*) FROM t_book_chapter WHERE book_id = b_id AND chapter_ispay = 1);
#总均订 = 订阅数 / 付费章节数
SET @order_all_num = @order_num / @chapter_num;
END IF;
#订阅人数
IF (SELECT COUNT(*) FROM t_member_order_book WHERE book_id = b_id AND LEFT(createtime,10) = b_day) > 0 THEN
SET @order_book_num = (SELECT COUNT(*) FROM t_member_order_book WHERE book_id = b_id AND LEFT(createtime,10) = b_day);
#人均订 = 订阅数 / 订阅人数
SET @order_per_capita_num = @order_num / @order_book_num;
END IF;
#真实点击数
IF (SELECT COUNT(*) FROM t_book_click WHERE book_id = b_id AND LEFT(click_time,10) = b_day) > 0 THEN
SET @book_clicknum = (SELECT COUNT(*) FROM t_book_click WHERE book_id = b_id AND LEFT(click_time,10) = b_day);
END IF;
#真实点击人数
IF (SELECT COUNT(DISTINCT member_id) FROM t_book_click WHERE book_id = b_id AND LEFT(click_time,10) = b_day) > 0 THEN
SET @click_num = (SELECT COUNT(DISTINCT member_id) FROM t_book_click WHERE book_id = b_id AND LEFT(click_time,10) = b_day);
#转订率 = 订阅人数 / 真实点击人数
SET @orderRate = @order_book_num / @click_num;
END IF;
INSERT INTO t_book_statistical(
book_day,
book_id,
book_name,
book_gender,
order_num,
order_all_num,
order_book_num,
order_per_capita_num,
book_clicknum,
click_num,
orderRate
) VALUES(
b_day,
b_id,
@book_name,
@book_gender,
@order_num,
@order_all_num,
@order_book_num,
@order_per_capita_num,
@book_clicknum,
@click_num,
@orderRate
);
# 游标往后移(相当于C里面的指针);
FETCH id_list INTO b_day,b_id;
END WHILE;
# 关闭游标
CLOSE id_list;
END $$
重新定义";“为一句sql的结束标志,取消之前定义”$$"的所代表意义;
delimiter ;
调用创建的函数
CALL bookStatistical();