Работа с MySQL
Версия от 14:09, 14 февраля 2020; 193.16.101.5 (обсуждение) (→Открепить айпишники от юзеров, которые отключены давно)
Удалить дубликаты из таблицы
/* 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 WHERE users.disabled > 0 and users.disable_date < '2017-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;