Несколько полезных SQL запросов

Технические вопросы по UTM 5.0
Закрыто
avegad
Сообщения: 54
Зарегистрирован: Чт сен 20, 2007 08:39

Несколько полезных SQL запросов

Сообщение avegad »

Биллинг: UTM 5.2.0-002
SQL: Mysql


Отчёт по платежам(готовый)

Код: Выделить всё

SELECT account_id, payment_incurrency, payment_absolute,FROM_UNIXTIME&#40;actual_date&#41;, method, who_receive, comments_for_user, comments_for_admins FROM payment_transactions WHERE actual_date>=UNIX_TIMESTAMP&#40;'2008-04-24 00&#58;00&#58;00'&#41; AND actual_date<=UNIX_TIMESTAMP&#40;'2008-04-24 17&#58;30&#58;00'&#41; ORDER BY actual_date DESC
Список $accounts принадлежащих $tariff

Код: Выделить всё

select account_id, tariff_id , is_deleted from account_tariff_link where tariff_id=$tariff AND is_deleted=0
Выборка трафика $account и $ip принадлежащего $tariff за $DATE

Код: Выделить всё

SELECT discount_transactions_iptraffic_all.account_id, SUM&#40;bytes&#41;, inet_ntoa&#40;ip_groups.ip & 0xFFFFFFFF&#41; AS ip FROM discount_transactions_iptraffic_all INNER JOIN account_tariff_link INNER JOIN users INNER JOIN ip_groups WHERE discount_transactions_iptraffic_all.discount_date>=UNIX_TIMESTAMP&#40;'2008-09-02 00&#58;00&#58;00'&#41; AND discount_transactions_iptraffic_all.discount_date<=UNIX_TIMESTAMP&#40;'2008-09-02 23&#58;59&#58;59'&#41; AND discount_transactions_iptraffic_all.account_id=account_tariff_link.account_id AND discount_transactions_iptraffic_all.t_class='10' AND account_tariff_link.is_deleted='0' AND account_tariff_link.tariff_id='12' AND users.id=account_tariff_link.account_id AND ip_groups.uname=users.login AND ip_groups.is_deleted=0 GROUP BY t_class,account_tariff_link.account_id,slink_id order by account_tariff_link.account_id;
Поиск IP принадлежащих Логину(использую для того что бы узнать какой у абонента когда то был IP)

Код: Выделить всё

select inet_ntoa&#40;ip_groups.ip & 0xFFFFFFFF&#41; AS ip,uname FROM ip_groups where uname="goblin";

Выборка IP Л.С и Логина с группировкой по IP

Код: Выделить всё

select inet_ntoa&#40;ip_groups.ip & 0xFFFFFFFF&#41; AS ip,users.id,ip_groups.uname FROM users INNER JOIN service_links ON users.id=service_links.user_id INNER JOIN ip_groups ON users.login=ip_groups.uname WHERE ip_groups.is_deleted=0 AND users.is_deleted=0 AND service_links.is_deleted=0;
Последний раз редактировалось avegad Вт окт 28, 2008 14:33, всего редактировалось 4 раза.

Аватара пользователя
MaxDM
Сообщения: 312
Зарегистрирован: Пн апр 03, 2006 10:26
Контактная информация:

Сообщение MaxDM »

Выборка логинов, которые меняют тарифный план.

Код: Выделить всё

SELECT u.login as login, inet_ntoa&#40;ig.ip&#41; as ip, ig.mac as mac, &#40;SELECT name from tariffs where id=atl.tariff_id&#41; as t_old, &#40;SELECT name from tariffs where id=atl.next_tariff_id&#41; as t_new FROM accounts a, service_links sl, iptraffic_service_links il, ip_groups ig, users u, account_tariff_link atl, tariffs t WHERE sl.account_id=a.id AND sl.id=il.id AND il.ip_group_id=ig.ip_group_id AND u.basic_account=a.id AND atl.account_id = a.id AND t.id = atl.tariff_id AND atl.tariff_id != atl.next_tariff_id AND sl.is_deleted=0 AND il.is_deleted=0 AND ig.is_deleted=0 AND a.is_deleted=0 AND u.is_deleted=0 AND tl.is_deleted=0 order by login;

SOLDIER
Сообщения: 649
Зарегистрирован: Чт мар 16, 2006 18:07

Сообщение SOLDIER »

MaxDM писал(а):Выборка логинов, которые меняют тарифный план.
SELECT u.login as login, inet_ntoa(ig.ip) as ip, ig.mac as mac, (SELECT name from tariffs where id=atl.tariff_id) as t_old, (SELECT name from tariffs where id=atl.next_tariff_id) as t_new FROM accounts a, service_links sl, iptraffic_service_links il, ip_groups ig, users u, account_tariff_link atl, tariffs t WHERE sl.account_id=a.id AND sl.id=il.id AND il.ip_group_id=ig.ip_group_id AND u.basic_account=a.id AND atl.account_id = a.id AND t.id = atl.tariff_id AND atl.tariff_id != atl.next_tariff_id AND sl.is_deleted=0 AND il.is_deleted=0 AND ig.is_deleted=0 AND a.is_deleted=0 AND u.is_deleted=0 AND tl.is_deleted=0 order by login;
1. ошибка. вместо отмеченного жирным (tl)- atl.
2. При выдаче запроса (с исправленным atl): #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

Аватара пользователя
MaxDM
Сообщения: 312
Зарегистрирован: Пн апр 03, 2006 10:26
Контактная информация:

Сообщение MaxDM »

Вот этот запрос только что проверил. UTM 5.2.1-006. PostgreSQL 8.2.7.

Код: Выделить всё

SELECT 
u.login as login, 
inet_ntoa&#40;ig.ip&#41; as ip, 
ig.mac as mac, 
&#40;SELECT name from tariffs where id=atl.tariff_id&#41; as t_old, 
&#40;SELECT name from tariffs where id=atl.next_tariff_id&#41; as t_new
FROM 
accounts a, 
service_links sl, 
iptraffic_service_links il, 
ip_groups ig, 
users u, 
account_tariff_link atl, 
tariffs t
WHERE 
sl.account_id=a.id 
AND 
sl.id=il.id 
AND 
il.ip_group_id=ig.ip_group_id 
AND 
u.basic_account=a.id 
AND 
atl.account_id = a.id
AND
t.id = atl.tariff_id 
AND 
atl.tariff_id != atl.next_tariff_id 
AND 
sl.is_deleted=0 AND il.is_deleted=0 AND ig.is_deleted=0 AND a.is_deleted=0 AND u.is_deleted=0 AND atl.is_deleted=0
order by login;

SOLDIER
Сообщения: 649
Зарегистрирован: Чт мар 16, 2006 18:07

Сообщение SOLDIER »

А я вот этот :) :

Код: Выделить всё

SELECT u.login as login, inet_ntoa&#40;ig.ip&#41;, atl.tariff_id as t_old, atl.next_tariff_id as t_new FROM accounts a, service_links sl, iptraffic_service_links il, ip_groups ig, users u, account_tariff_link atl, tariffs t WHERE sl.account_id=a.id AND sl.id=il.id AND il.ip_group_id=ig.ip_group_id AND u.basic_account=a.id AND atl.account_id = a.id AND t.id = atl.tariff_id AND atl.tariff_id != atl.next_tariff_id AND sl.is_deleted=0 AND il.is_deleted=0 AND ig.is_deleted=0 AND a.is_deleted=0 AND u.is_deleted=0 AND atl.is_deleted=0 order by login
Такое ощущение, что у меня (версия мускула 5.0.60) либо вложенные селекты не пашут (почему-то), либо ошибка в синтаксисе

Аватара пользователя
MaxDM
Сообщения: 312
Зарегистрирован: Пн апр 03, 2006 10:26
Контактная информация:

Сообщение MaxDM »

Положительный баланс и незаблокированные пользователи.

Код: Выделить всё

SELECT 
u.login as login, 
inet_ntoa&#40;ig.ip&#41; as ip
FROM 
accounts a, 
service_links sl, 
iptraffic_service_links il, 
ip_groups ig, 
users u, 
account_tariff_link atl, 
tariffs t
WHERE
sl.account_id=a.id 
AND 
sl.id=il.id 
AND 
il.ip_group_id=ig.ip_group_id 
AND 
u.basic_account=a.id 
AND 
atl.account_id = a.id
AND
t.id = atl.tariff_id
AND
a.balance > -10.0
AND
a.is_blocked=0
AND 
sl.is_deleted=0 AND il.is_deleted=0 AND ig.is_deleted=0 AND a.is_deleted=0 AND u.is_deleted=0 AND atl.is_deleted=0
order by login;

SOLDIER
Сообщения: 649
Зарегистрирован: Чт мар 16, 2006 18:07

Сообщение SOLDIER »

Тьфу ты! А Постгрес-то я и не приметил. :) UTM у меня 5.2.1-004. Но это не важно в данной ситуации.

avegad
Сообщения: 54
Зарегистрирован: Чт сен 20, 2007 08:39

Сообщение avegad »

SOLDIER писал(а):Тьфу ты! А Постгрес-то я и не приметил. :) UTM у меня 5.2.1-004. Но это не важно в данной ситуации.
Давайте постить версию биллинга и тип базы данных
а то так и заблудиться не долго

Instruktor
Сообщения: 131
Зарегистрирован: Ср авг 10, 2005 21:32
Откуда: Москва

Сообщение Instruktor »

поиск login, которому принадлежит $ip

Код: Выделить всё

SELECT users.login FROM users,accounts,service_links,iptraffic_service_links,ip_groups
WHERE users.is_deleted=0
AND users.basic_account=accounts.id
AND accounts.is_deleted=0
AND accounts.id=service_links.account_id
AND service_links.is_deleted=0
AND service_links.id=iptraffic_service_links.id
AND iptraffic_service_links.is_deleted=0
AND iptraffic_service_links.ip_group_id=ip_groups.ip_group_id
AND ip_groups.is_deleted=0
AND inet_ntoa&#40;ip_groups.ip & 0xFFFFFFFF&#41;="$ip"

lancelot
Сообщения: 248
Зарегистрирован: Вс янв 23, 2005 16:28

Сообщение lancelot »

С помощью админки можно сделать общий отчет по VPN сессиям
там есть столбец Вызывающий абонент там IP адрес.


как сделать SQL запрос поиска по IP адресу в столбце Вызывающий абонент что бы показало соответствие Вызывающий абонент=Номер договора

proto
Сообщения: 23
Зарегистрирован: Вт мар 11, 2008 12:42

Сообщение proto »

Товарищи гуру помогите. В mysql я не ас. Написал запросик, для выдёргивания IP абонентов имеющих определённый тариф:

Код: Выделить всё

SELECT INET_NTOA&#40;ip_groups.ip & 0xFFFFFFFF&#41; FROM account_tariff_link, service_links, iptraffic_service_links, ip_groups WHERE account_tariff_link.account_id = service_links.account_id AND service_links.id = iptraffic_service_links.id AND iptraffic_service_links.ip_group_id = ip_groups.ip_group_id AND users.is_deleted=0 AND account_tariff_link.is_deleted=0 AND service_links.is_deleted=0 AND iptraffic_service_links.is_deleted=0 AND ip_groups.is_deleted=0 AND &#40;account_tariff_link.tariff_id=31 OR account_tariff_link.tariff_id=42 OR account_tariff_link.tariff_id=43 OR account_tariff_link.tariff_id=52 OR account_tariff_link.tariff_id=53 OR account_tariff_link.tariff_id=54 OR account_tariff_link.tariff_id=55 OR account_tariff_link.tariff_id=56 OR account_tariff_link.tariff_id=61 OR account_tariff_link.tariff_id=62 OR account_tariff_link.tariff_id=63 OR account_tariff_link.tariff_id=64 OR account_tariff_link.tariff_id=65 OR account_tariff_link.tariff_id=66 OR account_tariff_link.tariff_id=75 OR account_tariff_link.tariff_id=76 OR account_tariff_link.tariff_id=77 OR account_tariff_link.tariff_id=78 OR account_tariff_link.tariff_id=84 OR account_tariff_link.tariff_id=85 OR account_tariff_link.tariff_id=86 OR account_tariff_link.tariff_id=87 OR account_tariff_link.tariff_id=93 OR account_tariff_link.tariff_id=94 OR account_tariff_link.tariff_id=95 OR account_tariff_link.tariff_id=96&#41;;
Время на выполнение этого запроса уходит почти 4 часа!
Машинка но которой крутится база 4-ёх процессорная, FreeBSD 7.0.
Чувствую, что где-то у меня запрос не оптимизирован :(
Можно конечно через админку, но вся соль в том, что бы получать списко ip в файл (для скрипта нужно) и запускать планируется раз в 1-2 дня.

Аватара пользователя
Mish!
Сообщения: 1
Зарегистрирован: Чт янв 24, 2008 08:50

Сообщение Mish! »

proto писал(а):Товарищи гуру помогите. …
Время на выполнение этого запроса уходит почти 4 часа!
«To find the bottleneck, stop guessing and start measuring» ©
0. «account_tariff_link.tariff_id=94 OR трам-парам» => account_tariff_link.tariff_id in (1,2,3,4)
1. Сделайте индексы по полям, которые участвуют в связывании; используйте составные индексы, например (account_id,is_deleted)
2. Напишите перед запросом слово explain и убедитесь что там нет слова ALL

proto
Сообщения: 23
Зарегистрирован: Вт мар 11, 2008 12:42

Сообщение proto »

Mish! писал(а):
proto писал(а):Товарищи гуру помогите. …
Время на выполнение этого запроса уходит почти 4 часа!
«To find the bottleneck, stop guessing and start measuring» ©
0. «account_tariff_link.tariff_id=94 OR трам-парам» => account_tariff_link.tariff_id in (1,2,3,4)
1. Сделайте индексы по полям, которые участвуют в связывании; используйте составные индексы, например (account_id,is_deleted)
2. Напишите перед запросом слово explain и убедитесь что там нет слова ALL
Спасибо, буду пробывать.

X-Disa
Сообщения: 24
Зарегистрирован: Пн июл 23, 2007 10:39

Сообщение X-Disa »

proto писал(а):Время на выполнение этого запроса уходит почти 4 часа!
Практика показывает, что при большом количестве объединяемых таблиц, mysql начинает неплохо так тормозить :) Попробуйте выполнить, например, два запроса, а остальные — в цикле по результатам.

mikkey finn
Сообщения: 1612
Зарегистрирован: Пт ноя 10, 2006 15:23

Сообщение mikkey finn »

или подкрутить размер буфера для джойнов, буфера для таблиц. чтоб мускул вертел данные максимально в памяти, а не создавал файлы на диске с сортировкой через диск.

Закрыто