Работа с MySQL
Версия от 14:56, 21 января 2022; 192.168.250.4 (обсуждение) (→Посчитать уменьшение дохода, если убрать правило 14-ти дней)
Содержание
- 1 Удалить дубликаты из таблицы
- 2 Посчитать уменьшение дохода, если убрать правило 14-ти дней
- 3 СЧИТАЕТ НЕКОРРЕКТНО!!!
- 4 Найти юзеров, у которых есть 2+ одинаковых адреса
- 5 Открепить айпишники от юзеров, которые отключены давно
- 6 Найти пропущенные Ip у коммутаторов
- 7 Найти свободные диапазоны UID, выполнять в DBeaver
- 8 Найти свободные диапазоны в номерах вланов, выполнять в DBeaver
- 9 Выбор юзеров вместе в полными адресами в файл
- 10 Выбор юзеров без адреса
- 11 Выбор логинов, ФИО, телефонов и адресов отключенных юзеров в файл
- 12 Изменение логина юзера
- 13 Все дома, в которых есть хотя бы 1 коммутатор, но при этом меньше 4-х активных абонентов
Удалить дубликаты из таблицы
/* 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, выполнять в DBeaver
-- 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
Найти свободные диапазоны в номерах вланов, выполнять в DBeaver
-- Select gaps in Vlan number ranges SELECT gaps.*, gaps.gap_end - gaps.gap_start + 1 AS gap_size 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 = number, 0, @rownum := number) AS got FROM (SELECT @rownum := 0) AS a JOIN Vlan ORDER BY number ) AS z WHERE z.got != 0 UNION SELECT MAX(number) + 1 AS gap_start, 4096 AS gap_end FROM Vlan ) AS gaps WHERE gap_start <= gap_end AND gap_end >= 1 -- if you need a specific range ORDER BY gap_size DESC;
Выбор юзеров вместе в полными адресами в файл
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;
Выбор логинов, ФИО, телефонов и адресов отключенных юзеров в файл
SELECT u.user, u.lastname, u.firstname, u.patronymic, u.disabled, u.disable_date, ms.name, mh.number, mf.number, GROUP_CONCAT(ph.number) FROM users u, map_streets ms, map_houses mh, map_porches mp, map_flats mf, map_flat_uid mfu, users_phones up, phones ph 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 up.uid=u.uid AND ph.id = up.phone_id AND u.disabled > 0 AND disable_date > '2020-09-30' AND disable_date < '2020-11-01' GROUP BY u.user ORDER BY ms.name INTO OUTFILE '/var/db/mysql/disabled.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
Изменение логина юзера
Т.к в экшенс записи пишутся по логину, а не по юид, надо во всех экшенсах обновить логины
update users set user='NEW_USERNAME' where user='OLD_USERNAME'; update zayavki set login ='NEW_USERNAME' where login ='OLD_USERNAME'; update actions set user ='NEW_USERNAME' where user='OLD_USERNAME';
Все дома, в которых есть хотя бы 1 коммутатор, но при этом меньше 4-х активных абонентов
SELECT ms.name street, house_with_commutator.number house, house_with_low_active_users.active_users FROM ( SELECT mh.id, mh.number, mh.street_id FROM map_houses mh INNER JOIN map_porches mp ON mp.house_id = mh.id INNER JOIN map_flats mf ON mf.porch_id = mp.id INNER JOIN NetworkNode nn ON nn.flat_id = mf.id INNER JOIN Commutator c ON c.networkNode_id = nn.id GROUP BY mh.id ) house_with_commutator INNER JOIN ( SELECT mh.id, COUNT(u.uid) active_users FROM map_houses mh INNER JOIN map_porches mp ON mp.house_id = mh.id INNER JOIN map_flats mf ON mf.porch_id = mp.id INNER JOIN map_flat_uid mfu ON mfu.flat_id = mf.id INNER JOIN users u ON u.uid = mfu.uid WHERE u.disabled = 0 GROUP BY mh.id HAVING active_users < 4 ) house_with_low_active_users ON house_with_low_active_users.id = house_with_commutator.id INNER JOIN map_streets ms ON ms.id = house_with_commutator.street_id ORDER BY ms.name, house_with_commutator.number;