SQL по выводу постоянных пользователей

← →
DelphiN! © (2007-10-19 12:45) [0]

Есть таблицы CASEBASE и USERS
с полями

таблица: CASEBASE
Поля: UserName_ Time_

Содержит: список всех операций пользователей
UserName_ - имя пользователя
Time_ - время начала операции
-----------

Таблица: Users
Поля: UserName_ RegistredTime_

Содержит: Время регистрации каждого пользователя
UserName_ имя пользователя
RegistredTime_ время регистрации

------------------

Необходимо вывести список пользователей,которые сделали более 2х операций за период времени, при этом время регистрации пользователей, попадающих под условие должно быть более 30ти дней от времени операции

Вот что у меня получилось :

SELECT UserName_, count (distinct EXTRACT(DAY FROM Time_)||"."||EXTRACT(MONTH FROM Time_)||"."||EXTRACT(YEAR FROM Time_)) FROM CaseBase WHERE (TIME_ > "15.08.2007")and(TIME_ < "15.09.2007")and (exists (select REGISTREDTIME_ from users where (casebase.UserName_ = users.UserName_)and((casebase.time_ - users.registredtime_)>30))) GROUP BY cardno_ having count (distinct EXTRACT(DAY FROM Time_)||"."||EXTRACT(MONTH FROM Time_)||"."||EXTRACT(YEAR FROM Time_)) > 2 ORDER BY 2

Однако данный запрос выводит пустоту, где у меня ошибка?

← →
DelphiN! (2007-10-19 13:11) [1]

Разобрался, у меня интервал времени был неверно указан просто ...

Тогда другой вопрос. Данный SQL выполняется очень долго, как можно его ускорить?

← →
Маша Шрайбер © (2007-10-19 13:12) [2]

А запрос-то вообще работает? Ведь как-то странно, выборка одного поля, группировка по другому.

← →
DelphiN! (2007-10-19 13:17) [3]

> Маша Шрайбер © (19.10.07 13:12) [2]

Упс, извеняюсь, просто на форуме изменил реальные названия на более понятные, и вконце забыл

SELECT UserName_, count (distinct EXTRACT(DAY FROM Time_)||"."||EXTRACT(MONTH FROM Time_)||"."||EXTRACT(YEAR FROM Time_)) FROM CaseBase WHERE (TIME_ > "15.08.2007")and(TIME_ < "15.10.2007")and (exists (select REGISTREDTIME_ from users where (casebase.UserName_ = users.UserName_)and((casebase.time_ - users.registredtime_)>30))) GROUP BY UserName_ having count (distinct EXTRACT(DAY FROM Time_)||"."||EXTRACT(MONTH FROM Time_)||"."||EXTRACT(YEAR FROM Time_)) > 2 ORDER BY 2

← →
Sergey13 © (2007-10-19 13:19) [4]

> [0] DelphiN! © (19.10.07 12:45)

Может я не вник в суть, но зачем вычислять
count (distinct EXTRACT(DAY FROM Time_)||"."||EXTRACT(MONTH FROM Time_)||"."||EXTRACT(YEAR FROM Time_))
вместо просто count(*) если надо просто посчитать количество операций?

← →
Правильный_Вася (2007-10-19 13:25) [5]

индексы по дате есть?

← →
DelphiN! (2007-10-19 13:28) [6]

> Sergey13 © (19.10.07 13:19) [4]

Не уточнил, по условию нужно считать только колличество дней, в которые производились операции.

Тоесть если в один день выполнилось 4 операции, а на второй день еще 3, нужно подсчитать их как 2 операции(2 уникальных дня,в которые производились операции)

← →
Sergey13 © (2007-10-19 13:50) [7]

> [6] DelphiN! (19.10.07 13:28)

Какой диалект, в Time_ реально время есть?

← →
DelphiN! (2007-10-19 14:09) [8]

> Sergey13 © (19.10.07 13:50) [7]

> Какой диалект, в Time_ реально время есть?

Dialect 1

Да, в Time_ есть дата и время

← →
Маша Шрайбер © (2007-10-19 14:16) [9]

чуть быстрее select 0 from вместо select REGISTREDTIME_ from

>> DelphiN! © (19.10.07 12:45)

А это принципиально, чтобы одним запросом?

← →
Sergey13 © (2007-10-19 14:17) [10]

> [8] DelphiN! (19.10.07 14:09)

Попробуй в таком варианте

SELECT UserName_, count (distinct EXTRACT(DAY FROM Time_)||"."||EXTRACT(MONTH FROM Time_)||"."||EXTRACT(YEAR FROM Time_)) FROM CaseBase, Users WHERE (TIME_ > "15.08.2007")and(TIME_ < "15.10.2007")and (casebase.UserName_ = users.UserName_) and (casebase.time_ - users.registredtime_)>30 GROUP BY UserName_ having count (distinct EXTRACT(DAY FROM Time_)||"."||EXTRACT(MONTH FROM Time_)||"."||EXTRACT(YEAR FROM Time_)) > 2 ORDER BY 2

← →
DelphiN! (2007-10-19 14:23) [11]

> Маша Шрайбер © (19.10.07 14:16) [9]

> А это принципиально, чтобы одним запросом?

Данный запрос должна выполнять не только специальная программа для него, он также должен легко выполняться в любом SQL редакторе(например IbExpert).

← →
Маша Шрайбер © (2007-10-19 14:34) [12]

>> DelphiN! (19.10.07 14:23) [11]

Тогда м.б. стоит реализовать данную логику в ХП. Это позволит гибко оптимизировать выполнение сего [0] действа, в первую очередь по времени.

← →
DelphiN! (2007-10-19 14:39) [13]

> Sergey13 © (19.10.07 14:17) [10]

> Попробуй в таком варианте

Данный запрос также выполняется очень долго ... (

> Маша Шрайбер © (19.10.07 14:34) [12]

Что такое ХП?

Как это реализовать?

← →
Sergey13 © (2007-10-19 14:44) [14]

> [13] DelphiN! (19.10.07 14:39)

Много ли записей в таблице CaseBase и есть ли индекс по Time_?

← →
DelphiN! (2007-10-19 15:09) [15]

> Sergey13 © (19.10.07 14:44) [14]

Индексы по полю Time в таблицах Users и CaseBase имеются

В таблице CASEBASE сейчас 100 000 записей в данный момент
Каждый месяц прибавляется примерно по столько же

В таблице Users - 5 000 записей в данный момент
Каждый месяц прибавляется примерно по столько же

← →
Маша Шрайбер © (2007-10-19 15:13) [16]

>> DelphiN! (19.10.07 14:39) [13]
>> Что такое ХП?

Хранимая процедура. Вы разве еще не освоили www.ibase.ru ???

← →
Sergey13 © (2007-10-19 15:15) [17]

> [15] DelphiN! (19.10.07 15:09)

Если count (distinct EXTRACT(DAY FROM Time_)||"."||EXTRACT(MONTH FROM Time_)||"."||EXTRACT(YEAR FROM Time_))
заменить на count(*) скорость приемлемая?

← →
DelphiN! (2007-10-19 16:17) [18]

> Sergey13 © (19.10.07 15:15) [17]

нет, также долго ...

Мне кажется что данный запрос выполняется долго так как
в строках
(casebase.UserName_ = users.UserName_) and (casebase.time_ - users.registredtime_)

стоит выборка из поля Users столько раз, сколько записей подходят под условие
(CASEBASE.TIME_ > "15.08.2007")and(CASEBASE.TIME_ < "15.10.2007")

это приходится делать, так как в таблице CASEBASE нет поля User.RegistredTime_

> [18] DelphiN! (19.10.07 16:17)
> Мне кажется что данный запрос выполняется долго так как

В моем варианте [10] Sergey13 © (19.10.07 14:17) вроде не должно бы быть так. Поэтому и предложил тебе его. Может ты его неправильно переделал?
Давай сюда последний свой вариант и план его вполнения.

← →
DelphiN! (2007-10-20 13:37) [20]

> Sergey13 © (19.10.07 16:32) [19]

Открываю IbExpert, открываю базу, жму F12, вставляю в окно редактора это :

SELECT cardno_,
count (*)
FROM CaseBase, Users

WHERE (TIME_ > "15.08.2007")and(TIME_ < "15.10.2007")and (casebase.cardno_ = users.cardno_) and (casebase.time_ - users.registredtime_)>30
GROUP BY cardno_

having count (*) > 2
ORDER BY 2

Жму F9

И долго жду :-)

SELECT CB.cardno_, COUNT (*) FROM CaseBase CB JOIN Users U ON (CB.cardno_ = U.cardno_) AND ((CB.time_ - U.registredtime_) > 30 ) WHERE CB.time_ BETWEEN "15.08.2007" AND "15.10.2007" GROUP BY CB.cardno_ HAVING COUNT(*) > 2 ORDER BY 2
Какие есть индексы на
CB.cardno_
U.cardno_
CB.time_
?

← →
DelphiN! (2007-10-22 09:48) [22]

> Johnmen © (20.10.07 22:52) [21]

Выполняется более 15ти минут

Индексы следующие :

CASEBASE
___________________________________________________
0 CASEBASE_IDX2 CARDNO_ 0 1 Ascending 0,000158881477545946836
___________________________________________________
1 RDB$PRIMARY2 ID_ 1 1 Ascending 0
___________________________________________________
0 CASEBASE_IDX1 TIME_ 0 1 Ascending 0,0000102923013400868513
___________________________________________________

USERS
___________________________________________________
1 RDB$PRIMARY17 ID_ 1 1 Ascending 0,000213858002098277211
___________________________________________________
0 USERS_IDX1 REGISTREDTIME_ 0 1 Ascending 0,000213995284866541624
___________________________________________________

> [20] DelphiN! (20.10.07 13:37)

Все таки план выполнения покажи.

> DelphiN! (22.10.07 09:48) [22]
> Индексы следующие :

1. Не видна уникальность/неуникальность индексов.
2. Где индекс на USERS.cardno_?
3. Попробуй вынести проверку разности времен из условия соединения в WHERE.

SQL по выводу постоянных пользователей Найти похожие ветки