Работа с MySQL

Удалить дубликаты из таблицы

/* REMOVE DUPLICATE TABLE ROWS */
#drop table if exists tmp;
#create temporary table tmp (`id` int(11) NOT NULL);
#insert tmp (id)
SELECT a.id FROM SubscriberLine a
INNER JOIN (SELECT MIN(id) as minid, user_uid, flat_id, networkNode_id
		FROM SubscriberLine
		GROUP BY user_uid, flat_id, networkNode_id
		HAVING Count(1) > 1) AS b
	ON ((a.user_uid = b.user_uid OR a.user_uid IS NULL AND b.user_uid IS NULL)
		AND (a.flat_id = b.flat_id OR a.flat_id IS NULL AND b.flat_id IS NULL)
		AND (a.networkNode_id = b.networkNode_id OR a.networkNode_id IS NULL AND b.networkNode_id IS NULL)
		AND a.id != b.minid);
#delete from SubscriberLine where id in (select id from tmp);

CREATE TEMPORARY TABLE tmp AS (SELECT id from SubscriberLine group by flat_id,user_uid,networkNode_id HAVING COUNT(*) > 1);
DELETE from SubscriberLine where id in(select id from tmp);

Посчитать уменьшение дохода, если убрать правило 14-ти дней

# How much money we will lose if remove 14days-rule.
SET @start_date = '2016-09-01';
SET @end_date = '2016-10-01';
select a1.user_uid, a1.datetime as disable_date, a2.datetime as enable_date,
TIMESTAMPDIFF(SECOND, a1.datetime, a2.datetime) as seconds,
TIMESTAMPDIFF(DAY, a1.datetime, a2.datetime) as days,
p.fixed_cost as packet_cost,
(p.fixed_cost * TIMESTAMPDIFF(SECOND, a1.datetime, a2.datetime) / 2592000) as cost_for_seconds,
SUM((p.fixed_cost * TIMESTAMPDIFF(SECOND, a1.datetime, a2.datetime) / 2592000))
from admin_actions a1
inner join (
	SELECT user_uid, datetime from admin_actions
	where type = 8 and subtype = 0 and value_after = 'подключен' and datetime between @start_date and @end_date
) a2 on a1.user_uid = a2.user_uid
inner join users u on a1.user_uid = u.uid
inner join packets p on u.gid = p.gid
where a1.type = 8 and a1.subtype = 0 and a1.value_after = 'отключен' and a1.datetime between @start_date and @end_date
and a1.datetime < a2.datetime
and TIMESTAMPDIFF(DAY, a1.datetime, a2.datetime) < 14;

Найти юзеров, у которых есть 2+ одинаковых адреса

SELECT COUNT(*) AS repetitions, flat_id,uid from map_flat_uid group by flat_id,uid HAVING repetitions > 1;

Открепить айпишники от юзеров, которые отключены давно

update Ip INNER JOIN users ON users.uid=Ip.user_uid SET Ip.user_uid = NULL, Ip.mac = NULL 
WHERE users.disabled > 0 and users.disable_date < '2019-01-01' and Ip.subnet_id= xx ;

id подсети берем в ПМ, вкладка подсети

Найти пропущенные Ip у коммутаторов

SELECT INET_NTOA(INET_ATON(t1.Ip) + 1) AS empty_ip FROM Commutator AS t1 
LEFT JOIN Commutator AS t2 ON INET_ATON(t2.Ip) = INET_ATON(t1.Ip) +1 
WHERE t2.Ip IS NULL and INET_NTOA(INET_ATON(t1.Ip) + 1) IS NOT NULL order by INET_NTOA(INET_ATON(t1.Ip) + 1);

Найти свободные диапазоны UID

-- Select gaps between IDs
SELECT *
FROM (
	SELECT z.expected AS gap_start, IF(z.got - 1 > z.expected, z.got - 1, z.expected) AS gap_end
	FROM (
		SELECT @rownum := CONVERT(@rownum + 1, SIGNED INTEGER) AS expected, IF(@rownum = uid, 0, @rownum := uid) AS got
		FROM (SELECT @rownum := 0) AS a
		JOIN users
		ORDER BY uid
	) AS z
	WHERE z.got != 0
	UNION
	SELECT MAX(uid) + 1 AS gap_start, 65535 AS gap_end FROM users
) AS gaps
WHERE gap_start <= gap_end
	AND gap_end >= 10000; -- if you need a specific range

Выбор юзеров вместе в полными адресами в файл

select u.user, u.disabled,i.ip, ms.name, mh.number,mf.number 
from users u, map_streets ms, map_houses mh, map_porches mp, map_flats mf, map_flat_uid mfu, Ip i 
WHERE  mh.id = mp.house_id 
AND mp.id = mf.porch_id 
AND mf.id = mfu.flat_id 
AND mfu.uid = u.uid 
AND mh.street_id = ms.id 
AND i.user_uid=u.uid 
AND i.subnet_id = 116 
AND u.disabled = 0 order by ms.name 
INTO OUTFILE '/var/db/mysql/5sradio1.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

Выбор юзеров без адреса

select user from users u LEFT JOIN map_flat_uid mfu ON u.uid=mfu.uid WHERE u.gid NOT IN (2,15,34,49) AND mfu.uid IS NULL;

Изменение логина юзера

Т.к в экшенс записи пишутся по логину, а не по юид, надо во всех экшенсах обновить логины

update users set user='NEW_USERNAME' where user='OLD_USERNAME';
update actions set user ='NEW_USERNAME' where user='OLD_USERNAME';
update actions set user ='NEW_USERNAME' where user='OLD_USERNAME';