[решено] SQLite: поместить результаты запроса в нужные поля таблицы

[Griboed0ff]

Доброго времени суток! Недавно познакомился с базой sqlite, мне так понравилось, что я решил хранить и накапливать некоторые данные именно в базе. Получать эти данные я хотел бы сразу в нужном виде, но я пока не могу составлять сложные запросы.
Дано: в таблицу собираются данные о факте логона пользователя на пк. Информация: имя пк, логин пользователя, дата время, тип логона.
Компьютеров в таблице тысячи, пример только для одного из них (см.причину исправления сообщения).
WorkstationName TargetUserName TimeCreated logontype
name1 user1 21.09.2021 10:15 2
name1 user1 22.09.2021 14:19 2
name1 user1 21.09.2021 12:01 2
name1 user2 21.09.2021 15:06 2
name1 user2 22.09.2021 10:21 2
name1 user2 22.09.2021 15:01 2
name1 user2 22.09.2021 15:18 2
name1 user2 21.09.2021 15:59 2
name1 user2 22.09.2021 12:05 2
name1 user2 21.09.2021 10:32 2
name1 user2 21.09.2021 15:32 2
name1 user2 22.09.2021 10:24 2
name1 user2 21.09.2021 17:12 2
name1 user2 22.09.2021 15:51 2
name1 user3 21.09.2021 10:31 2
name1 user3 22.09.2021 10:33 2
name1 user3 22.09.2021 15:36 2
name1 user3 21.09.2021 14:11 2
name1 user3 22.09.2021 14:08 2
name1 user3 21.09.2021 10:32 2
name1 user3 22.09.2021 10:33 2
name1 user3 21.09.2021 12:07 2
name1 user3 22.09.2021 12:41 2
name1 user4 21.09.2021 14:44 2
name1 user4 22.09.2021 13:15 2

Задача: посчитать сколько раз вообще логинились на одном пк из спика, посчитать количество логонов на данном пк для каждого юзера, создать таблицу где поместим все эти данные в одну строку. Если пользователей одного пк больше чем 3, то оставить только 3 наибольших по кол-ву входов. Если пользователи имеют одинаковое кол-во входов, то выбрать по ближайшей дате логона. Учет вести только логоны не старше 100 дней, используя столбец даты времени. Все это для каждого ПК в таблице.
WorkstationName all logon user 1 user 1 logon user 2 user 2 logon user 3 user 3 logon
name1 26 user2 11 user3 9 user1 3


создать табицу из запроса могу:
CREATE TABLE IF NOT EXISTS users_result AS

посчитать сколько логонов всего для одного ПК могу:
select WorkstationName, Count(*) from pc_users group by WorkstationName, WorkstationName;
ответ на запрос:
U240-3;2
U240-4;1
U240-5;1
U240-6;2
U240-7;2

посчитать сколько логонов для каждого из пользователей могу:
select WorkstationName, TargetUserName, Count(*) from test group by WorkstationName, TargetUserName, TargetUserName;
ответ на запрос:
U431-2;U431;3
U431-3;U431;2
U431-2;U431;2
U431-4;U431;3

Отфильтровать записи младше 100 дней, скорее всего можно будет через Where.

А вот дальше начинаются вопросы. Как создать таблицу в нужном виде, учитывая условия? Возможно прибегнуть к временным таблицам? Буду рад любой помощи!

[Iska]

создать таблицу где поместим все эти данные в одну строку. »
Не надо помещать. Количество входов получаете из таблицы обычным запросом с группировкой.

только 3 наибольших по кол-ву входов. »
SELECT TOP 3 … ORDER BY …
Update: предикат TOP отсутствует в данном диалекте — см. замечание коллеги El Sanchez ниже.

[Griboed0ff]

Не надо помещать. »
но мне нужно именно такое представление таблицы. Если не получится через базу, то нужно будет снова делать это руками через excel

[Iska]

Griboed0ff, я имею в виду, что данное актуальное (!) представление можно в любой момент получить запросом.

[El Sanchez]

SELECT TOP 3 … ORDER BY … »
Iska, такого в sqlite нет.

Griboed0ff,

SELECT WorkstationName,
sum(user_logon_count) OVER () AS all_logon,
nth_value(TargetUserName, 1) OVER () AS "user 1",
nth_value(user_logon_count, 1) OVER () AS "user 1 logon",
nth_value(TargetUserName, 2) OVER () AS "user 2",
nth_value(user_logon_count, 2) OVER () AS "user 2 logon",
nth_value(TargetUserName, 3) OVER () AS "user 3",
nth_value(user_logon_count, 3) OVER () AS "user 3 logon"
FROM (
SELECT *
FROM (
SELECT *,
printf('%s-%s-%s %s:00',
substr(TimeCreated, 7, 4),
substr(TimeCreated, 4, 2),
substr(TimeCreated, 1, 2),
substr(TimeCreated, 12)
) AS time_created,
count(1) OVER (PARTITION BY TargetUserName) AS user_logon_count
FROM pc_users
WHERE WorkstationName = 'name1' AND
time_created > datetime('now', '-100 days')
ORDER BY user_logon_count DESC, time_created DESC
) GROUP BY TargetUserName
ORDER BY user_logon_count DESC LIMIT 3
) LIMIT 1

[Iska]

El Sanchez, я вообще охренел, когда увидел * и просто имена полей в выборке с группировкой :). Кстати, какая строка из нескольких, попавших в группировку, в этом случае берётся для таких полей?

[El Sanchez]

я вообще охренел, когда увидел * и просто имена полей в выборке с группировкой »
― Что это у тебя?
― Надо!
(из к/ф "Джентельмены удачи")
Кстати, какая строка из нескольких, попавших в группировку, в этом случае берётся для таких полей? »
Iska, здесь 2 уровня вложенности SELECT. Во 2-ом выбираются строки не старше 100 дней и сортируются по убыванию сначала по количеству логонов, а потом по штампу времени логона. Если здесь делать группировку по пользователю, то от сортировки будет мало толку, в результат попадёт первая строка из группы неотсортированных строк, поэтому отсортированные строки поступают в SELECT 1-го уровня. Так как строки уже отсортированы при группировке первыми строками в группах будут строки с ближайшим штампом времени, результат сортируется по убыванию по количеству логонов, а затем ограничивается до 3 строк. Внешний SELECT формирует окончательный результат.

[Iska]

El Sanchez, нет, я об общем принципе, который видел в соседней теме (http://forum.oszone.net/post-2967094.html#post2967094). Например:
Field1 Field2 Field3
АААА ЯЯЯЯ Группа1
ББББ ЮЮЮЮ Группа2
ВВВВ ЭЭЭЭ Группа1
ГГГГ ФФФФ Группа2
ДДДД ХХХХ Группа2

Запрос:
SELECT *
FROM MyTable
GROUP BY Field3

Какие значения попадут в поля Field1 и Field2 обеих строк результирующей выборки подобного запроса в SQLite?

[El Sanchez]

Какие значения попадут в поля Field1 и Field2 обеих строк результирующей выборки подобного запроса в SQLite? »
Iska, АААА/ЯЯЯЯ для Группа1 и ББББ/ЮЮЮЮ для Группа2, т.к. это первые найденные строки при формировании групп.

я об общем принципе, который видел в соседней теме. »
Iska, до меня дошло, к чему вы клоните. С HAVING, как из моего примера, не работает в вашем.

[Griboed0ff]

Griboed0ff »
Попробовал ваш запрос, ошибки никакой не было, но и данных никаких нет. Позже я додумался сделать данные так же как и в своем примере, т.е. поместить данные только для одного имени ПК (name1), тогда заветный ответ на запрос появился. Получается, что ваш запрос работает только для одного имени ПК, а у меня их тысячи. Это все из-за неверное поставленного условия задачи мною.

[Griboed0ff]

я имею в виду, что данное актуальное (!) представление можно в любой момент получить запросом. »
я помещаю данные в таблицу, чтобы потом экспортировать их в csv, я пока не умею экспортировать данные запроса сразу в таблицу csv..

[El Sanchez]

я пока не умею экспортировать данные запроса сразу в таблицу csv. »
Griboed0ff, используемый вами SQLiteStudio умеет в экспорт в csv БД, таблицы или запроса (Tools - Export).

[Griboed0ff]

используемый вами SQLiteStudio умеет в экспорт в csv БД, таблицы или запроса (Tools - Export). » SQLiteStudio использую только для удобства просмотра данных, все команды ввожу через консоль. В дальнейшем, если получится составить запрос в этой теме, команды будет вводить скрипт powershell или через триггер сделаю. То есть я сделаю запрос, поместив данные во временную таблицу, экспортирую csv, удалю временную таблицу.

[Griboed0ff]

Извините, я не правильно привел пример данных, указав там только один пк, но у меня информация в таблице с множеством разных пк и получить информацию нужно о всех, что содержаться в таблице, а не только для "name1". Я пробовал вставлять в запрос другие имена пк и ответы получаются правильными. Даже на данном этапе я могу с помощью powershell помещать в запросы нужные имена, например взяв ту же выборку из AD по аушкам. Или создам запрос в таблицу pc_users для выявления всех имен пк без повторений, а потом буду их поочередно запрашивать, то получится таблица, которую нужно, со всеми ПК и информацией о логине пользователей. Но думаю, что это невероятные костыли в плане запросов.

[El Sanchez]

у меня информация в таблице с множеством разных пк и получить информацию нужно о всех, что содержаться в таблице, а не только для "name1". »
Griboed0ff,

SELECT *
FROM (
SELECT WorkstationName,
sum(user_logon_count) OVER win AS all_logon,
nth_value(TargetUserName, 1) OVER win AS "user 1",
nth_value(user_logon_count, 1) OVER win AS "user 1 logon",
nth_value(TargetUserName, 2) OVER win AS "user 2",
nth_value(user_logon_count, 2) OVER win AS "user 2 logon",
nth_value(TargetUserName, 3) OVER win AS "user 3",
nth_value(user_logon_count, 3) OVER win AS "user 3 logon"
FROM (
SELECT *, row_number() OVER (
PARTITION BY WorkstationName
ORDER BY user_logon_count DESC, time_created DESC) AS rn
FROM (
SELECT *,
printf('%s-%s-%s %s:00',
substr(TimeCreated, 7, 4),
substr(TimeCreated, 4, 2),
substr(TimeCreated, 1, 2),
substr(TimeCreated, 12)
) AS time_created,
count(1) OVER (PARTITION BY WorkstationName, TargetUserName) AS user_logon_count
FROM pc_users
WHERE time_created > datetime('now', '-100 days')
ORDER BY WorkstationName, user_logon_count DESC, time_created DESC
) GROUP BY WorkstationName, TargetUserName
) WHERE rn <= 3
WINDOW win AS (PARTITION BY WorkstationName)
) GROUP BY WorkstationName

[Griboed0ff]

Это то, что нужно! Пойду разбираться в этом запросе, чтобы хотя бы понять где и что происходит, чтобы далее самому составлять запросы.