SELECT
	stock_code,
	stock_name,
	count sub_num,
	(
		SELECT
			count
		FROM
			ji_stock_action ac2
		WHERE
			action != '减持'
		and
			ac2.stock_code = ji_stock_action.stock_code
		GROUP BY ac2.stock_code
	) plu_num,
	 (
		SELECT
			count
		FROM
			ji_stock_action ac2
		WHERE
			action != '减持'
		and
			ac2.stock_code = ji_stock_action.stock_code
		GROUP BY ac2.stock_code
	)- count diff,
	((
		SELECT
			count
		FROM
			ji_stock_action ac2
		WHERE
			action != '减持'
		and
			ac2.stock_code = ji_stock_action.stock_code
		GROUP BY ac2.stock_code
	)-count)/count*100 as rate
FROM
	ji_stock_action
WHERE
	action = '减持'

ORDER BY rate desc



INSERT INTO ji_stock_action (count,action,stock_name,stock_code,date) SELECT
	count(1) AS count,
	changename action,
	stock_name,
	stock_code,
	showdate date
FROM
	ji_stock
GROUP BY
	changename,
	stock_name,
  showdate
ORDER BY
	COUNT(1) DESC




INSERT INTO ji_stock_action_result (count,action,stock_name,stock_code,date,sub_num,plus_num)
SELECT
	sum(count) count,
	action,
	stock_name,
	stock_code,
	date,
	abs((
		SELECT
			count
		FROM
			ji_stock_action ac
		WHERE
			ac.action = '减持'
			and ac.stock_code = ji_stock_action.stock_code
			and ac.date = ji_stock_action.date
	)) sub_num,
(
		SELECT
			sum(count) as count
		FROM
			ji_stock_action ac
		WHERE
			ac.action != '减持'
			and ac.stock_code = ji_stock_action.stock_code
			and ac.date = ji_stock_action.date
	) plus_num
FROM
	ji_stock_action
GROUP BY
	stock_code,
	date


SELECT
	*,count(1) as quatatimes
FROM
	ji_stock_action_result
WHERE
	count>100
and date>'2019-12-01'
GROUP BY
stock_code
HAVING
rate>3

UPDATE ji_stock_action_result set rate = plus_num/sub_num