Гость
Войти | Профиль | Очистить
Map
Действия ...
Форумы / SQLite [игнор отключен] [закрыт для гостей] / Дополнение запроса для показа значений, не вошедших в условия изначального запроса. / 20 сообщений из 20, страница 1 из 1
13.05.2020, 13:16
    #39956798
Vizit0r
Vizit0r
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дополнение запроса для показа значений, не вошедших в условия изначального запроса.
Добрый день.

Есть таблица "social_interaction", поля
"PlayerID" INTEGER NOT NULL,
"GuestID" INTEGER NOT NULL,
"GuestName" TEXT,
"Date" datetime NOT NULL

Сделал запрос (дата и PlayerID для фильтра динамические):
Код: sql
1.
SELECT ROW_NUMBER() OVER(ORDER BY COUNT(GuestID)) AS NoId, *, COUNT(GuestID) AS Qty FROM social_interaction WHERE Date > ''2020-05-02'' AND PlayerID = 6935920 group by GuestName order by Qty


отлично отрабатывает.
(из Delphi срабатывает так, в Navicat условие по дате надо делать в виде Date > date('2020-05-02') )

Всего в таблице например 90 уникальных GuestID. Из них в запрос под условия попадает например 69.

Подскажите, как можно допилить запрос, чтобы записи, не попадающие под условия изначального запроса, добавить туда же с Qty 0 ? (да хоть как добавить, там я уже разберусь)

Дамп таблицы прицепил файлом. Файл упорно не цепляется, даже архивом. Выложил тут: stealth.od.ua/download/social_interaction.sql

Спасибо.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.05.2020, 17:29
    #39957004
VSVLAD
VSVLAD
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дополнение запроса для показа значений, не вошедших в условия изначального запроса.
Vizit0r,

Например, добавить union all и селект с условием where Date < ''2020-05-02'' AND PlayerID <> 6935920
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.05.2020, 22:13
    #39957141
Vizit0r
Vizit0r
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дополнение запроса для показа значений, не вошедших в условия изначального запроса.
VSVLAD
Vizit0r,

Например, добавить union all и селект с условием where Date < ''2020-05-02'' AND PlayerID <> 6935920


вопрос в том, даже у тех, что попадут в первый запрос есть поля с датой меньше заданной.

Но у кого-то есть заходы и старее, и новее, а у кого-то только старее.

Вот я и ломаю голову, как бы это так составить.


P.S. Выполняется такой запрос раз в несколько дней, так что вопрос производительности не стоит вообще.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.05.2020, 00:59
    #39957183
White Owl
White Owl
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дополнение запроса для показа значений, не вошедших в условия изначального запроса.
Vizit0r
VSVLAD
Vizit0r,

Например, добавить union all и селект с условием where Date < ''2020-05-02'' AND PlayerID <> 6935920


вопрос в том, даже у тех, что попадут в первый запрос есть поля с датой меньше заданной.

Но у кого-то есть заходы и старее, и новее, а у кого-то только старее.

Вот я и ломаю голову, как бы это так составить.


P.S. Выполняется такой запрос раз в несколько дней, так что вопрос производительности не стоит вообще.

https://www.sql.ru/forum/983207/kak-prosit-pomoshhi-s-zaprosom
Обрати внимание на пункты 2 и 4
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.05.2020, 06:23
    #39957210
Vizit0r
Vizit0r
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дополнение запроса для показа значений, не вошедших в условия изначального запроса.
White Owl
Vizit0r
пропущено...


вопрос в том, даже у тех, что попадут в первый запрос есть поля с датой меньше заданной.

Но у кого-то есть заходы и старее, и новее, а у кого-то только старее.

Вот я и ломаю голову, как бы это так составить.


P.S. Выполняется такой запрос раз в несколько дней, так что вопрос производительности не стоит вообще.

https://www.sql.ru/forum/983207/kak-prosit-pomoshhi-s-zaprosom
Обрати внимание на пункты 2 и 4


пункт 2 выполнен, в полном дампе есть и создание таблицы, и ее содержимое.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.05.2020, 07:32
    #39957219
Vizit0r
Vizit0r
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дополнение запроса для показа значений, не вошедших в условия изначального запроса.
все, слепил голема. Выглядит страшно, но работает правильно.

Код: sql
1.
2.
3.
SELECT ROW_NUMBER() OVER(ORDER BY COUNT(GuestID)) AS NoId, *, 0 AS Qty FROM social_interaction WHERE PlayerID = 6935920 AND  GuestId not in (SELECT GuestId FROM social_interaction WHERE PlayerID = 6935920 AND  Date > ''2020-05-07'') group by GuestName 
UNION 
SELECT ROW_NUMBER() OVER(ORDER BY COUNT(GuestID)) AS NoId, *, COUNT(GuestID) AS Qty FROM social_interaction WHERE PlayerID = 6935920 AND  Date > ''2020-05-07'' group by GuestName order by Qty


(финальная версия из Delphi перед отправкой в БД)

Нумерация NoId правда не сквозная вышла, сначала идут номера с первого запроса, потом опять с 1 записи из второго запроса - но это вообще не принципиальный вопрос.

Спасибо за наводку на мысль с union

Результат:
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.05.2020, 07:57
    #39957226
Cat2
Cat2
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дополнение запроса для показа значений, не вошедших в условия изначального запроса.
Vizit0r,

Оберните вложение в архив. Браузеры не дают скачивать расширение sql
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.05.2020, 12:55
    #39957370
Vizit0r
Vizit0r
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дополнение запроса для показа значений, не вошедших в условия изначального запроса.
приложил в архиве.

Но вопрос решен.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.05.2020, 16:45
    #39957565
Cat2
Cat2
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дополнение запроса для показа значений, не вошедших в условия изначального запроса.
Vizit0r
Но вопрос решен.

Эту корявость Вы называете "решением"?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.05.2020, 21:12
    #39957747
Cat2
Cat2
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дополнение запроса для показа значений, не вошедших в условия изначального запроса.
Вот так, сначала пишутся подобные запросы, а потом сайт госуслуг падает :(
Запрос здорового человека
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
SELECT PlayerId,
       GuestID,
       GuestName,
       min(Date) AS FirstDate,
       max(Date) AS LastDate,
       CASE WHEN Date > '2020-05-07' THEN 1 ELSE 0 END AS Qty
 FROM social_interaction
 WHERE PlayerId = 6935920
 GROUP BY PlayerId,
          GuestID,
          GuestName
 ORDER BY 5,4,3;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.05.2020, 09:44
    #39957861
Vizit0r
Vizit0r
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дополнение запроса для показа значений, не вошедших в условия изначального запроса.
Cat2,

Ого, это круто.
Большое спасибо.

P.S. Приложение для себя и еще пары человек, так что ничего бы не упало :)


авторЭту корявость Вы называете "решением"?
ну...оно работало :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.05.2020, 10:00
    #39957869
Vizit0r
Vizit0r
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дополнение запроса для показа значений, не вошедших в условия изначального запроса.
Cat2,

Посмотрел в ваш запрос, попытался его применить, и сообразил, что самого главного-то в нем и нет - кол-ва Qty заходов после заданной даты.

Попытался изменить

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
SELECT PlayerId,
       GuestID,
       GuestName,
       min(Date) AS FirstDate,
       max(Date) AS LastDate,
       CASE WHEN Date > Date('2020-05-07') THEN COUNT(GuestID) ELSE 0 END AS Qty
 FROM social_interaction
 WHERE PlayerId = 6935920
 GROUP BY PlayerId,
          GuestID,
          GuestName
 ORDER BY Qty;



ожидаемо COUNT(GuestID) возвращает все вхождения в базу, а не старше заданной даты.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.05.2020, 10:48
    #39957892
VSVLAD
VSVLAD
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дополнение запроса для показа значений, не вошедших в условия изначального запроса.
Vizit0r,

внутри Count можно сделать case when, если условие истино то guest_id, если ложно то null и count верно подсчитает по датам > указанной
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.05.2020, 11:34
    #39957914
Vizit0r
Vizit0r
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дополнение запроса для показа значений, не вошедших в условия изначального запроса.
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
SELECT PlayerId,
       GuestID,
       GuestName,
       min(Date) AS FirstDate,
       max(Date) AS LastDate,
       CASE WHEN Date > Date('2020-05-07') THEN COUNT(CASE WHEN Date > Date('2020-05-07') THEN GuestID ELSE null END) ELSE 0 END AS Qty
 FROM social_interaction
 WHERE PlayerId = 6935920
 GROUP BY PlayerId,
          GuestID,
          GuestName
 ORDER BY Qty;



Работает отлично, большое спасибо!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.05.2020, 11:46
    #39957919
Vizit0r
Vizit0r
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дополнение запроса для показа значений, не вошедших в условия изначального запроса.
господа знатоки, подскажите, почему 2 почти одинаковых запроса, только второй с двумя закомментированными полями в селекте - считают по-разному?
Оригинальный запрос (из поста выше)
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
SELECT PlayerId,
       GuestID,
       GuestName,
       min(Date) AS FirstDate,
       max(Date) AS LastDate,
       CASE WHEN Date > Date('2020-05-07') THEN COUNT(CASE WHEN Date > Date('2020-05-07') THEN GuestID ELSE null END) ELSE 0 END AS Qty
 FROM social_interaction
 WHERE PlayerId = 6935920
 GROUP BY PlayerId,
          GuestID,
          GuestName
 ORDER BY Qty;



guest, по которому расхождение:
PlayerId GuestID GuestName FirstDate LastDate Qty
6935920 1037342 Foxter93 2020-03-26 21:54:00.000 2020-05-08 07:24:00.000 1


Запрос с двумя закомментированными полями(FirstDate и LastDate)
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
SELECT PlayerId,
       GuestID,
       GuestName,
--       min(Date) AS FirstDate,
--       max(Date) AS LastDate,
       CASE WHEN Date > Date('2020-05-07') THEN COUNT(CASE WHEN Date > Date('2020-05-07') THEN GuestID ELSE null END) ELSE 0 END AS Qty
 FROM social_interaction
 WHERE PlayerId = 6935920
 GROUP BY PlayerId,
          GuestID,
          GuestName
 ORDER BY Qty;


PlayerId GuestID GuestName Qty
6935920 1037342 Foxter93 0


Почему так?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.05.2020, 12:23
    #39957938
VSVLAD
VSVLAD
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дополнение запроса для показа значений, не вошедших в условия изначального запроса.
Vizit0r,

Не так. Это:
Код: sql
1.
CASE WHEN Date > Date('2020-05-07') THEN COUNT(CASE WHEN Date > Date('2020-05-07') THEN GuestID ELSE null END) ELSE 0 END AS Qty


Заменить на:
Код: sql
1.
COUNT(CASE WHEN Date > Date('2020-05-07') THEN GuestID ELSE null END) AS Qty



Тогда последнего вопроса возникнуть не должно. Иначе у вас группировка неверно отрабатывает, а SQLite не говорит ошибок, если в Group By перечислены не все поля, которые используются в Select, но влияют на аггрегацию
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.05.2020, 12:56
    #39957951
Vizit0r
Vizit0r
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дополнение запроса для показа значений, не вошедших в условия изначального запроса.
Спасибо, поменял, отлично работает.

Теперь вопрос точно решен полностью
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.05.2020, 13:48
    #39957981
Cat2
Cat2
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дополнение запроса для показа значений, не вошедших в условия изначального запроса.
Vizit0r,

Во-первых, Вы нигде четко и ясно не писали, что такое Qty.
Во-вторых, в присланной таблице желаемых результатов были только 1 и 0.
В-третьих, присланный Вами дамп не содержал игроков, которые были в итоговой таблице и я не мог с уверенностью сказать, что мой запрос точно соответствует желаемому.

Ну и в-четвертых. Проще надо быть в запросах!!

Vizit0r
Спасибо, поменял, отлично работает.

Теперь вопрос точно решен полностью


Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
SELECT PlayerId,
       GuestID,
       GuestName,
       min(Date) AS FirstDate,
       max(Date) AS LastDate,
       sum(CASE WHEN Date > '2020-05-07' THEN 1 ELSE 0 END) AS Qty
 FROM social_interaction
 WHERE PlayerId = 6935920
 GROUP BY PlayerId,
          GuestID,
          GuestName
 ORDER BY 5,4,3;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.05.2020, 14:20
    #39958005
Cat2
Cat2
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дополнение запроса для показа значений, не вошедших в условия изначального запроса.
Vizit0r
Почему так?


Потому, что второй запрос неправильный.

SQlite вообще как-то странно обрабатывает агрегатирующий запросы.
Например, он позволяет такую конструкцию:

Код: sql
1.
2.
3.
4.
5.
6.
7.
SELECT PlayerId,
       GuestID,
       GuestName,
min(Date) AS FirstDate
 FROM social_interaction
 WHERE PlayerId = 6935920
 GROUP BY PlayerId



В других СУБД она не прошла бы на уровне проверки синтаксиса, а SQLite срабатывает так, как будто в нем есть функция FIRST

Так можно написать в некоторых СУБД

Код: sql
1.
2.
3.
4.
5.
6.
7.
SELECT PlayerId,
       first(GuestID) as GuestID,
       first(GuestName) as GuestName
min(Date) AS FirstDate
 FROM social_interaction
 WHERE PlayerId = 6935920
 GROUP BY PlayerId
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.05.2020, 14:20
    #39958006
Vizit0r
Vizit0r
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дополнение запроса для показа значений, не вошедших в условия изначального запроса.
Cat2
Vizit0r,

Во-первых, Вы нигде четко и ясно не писали, что такое Qty.
Во-вторых, в присланной таблице желаемых результатов были только 1 и 0.
В-третьих, присланный Вами дамп не содержал игроков, которые были в итоговой таблице и я не мог с уверенностью сказать, что мой запрос точно соответствует желаемому.

Ну и в-четвертых. Проще надо быть в запросах!!


1) мне казалось, что это избыточная информация, не хотел захламлять вопрос.
2) Виноват(
3) БД постоянно дополняется, так-то надо было конечно выполнять запросы на той же копии, что я сюда скидывал - но не сообразил.
4) Это единственный случай был за последние лет 15, когда моих примитивных познаний sql не хватило, гугл не помог, и пришлось просить помощи у профессионалов.

И да, вам отдельное спасибо.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / SQLite [игнор отключен] [закрыт для гостей] / Дополнение запроса для показа значений, не вошедших в условия изначального запроса. / 20 сообщений из 20, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 408ms
Закрыть
Start [/forum/topic.php?fid=54&mobile=1&tid=2008363]:
 0ms
get settings:
 16ms
get forum list:
 16ms
check forum access:
 1ms
check topic access:
 1ms
track hit:
 25ms
get topic data:
 7ms
get forum data:
 1ms
get page messages:
 333ms
get tp. blocked users:
 1ms
others: 7ms
total:  408ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]