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

Компьютерный форум OSzone.net (http://forum.oszone.net/index.php)

- Программирование и базы данных (http://forum.oszone.net/forumdisplay.php?f=21)

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

Доброго времени суток! Недавно познакомился с базой 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.

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

Цитата:

Цитата Griboed0ff

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

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

Цитата:

Цитата Griboed0ff

только 3 наибольших по кол-ву входов. »

Код:

SELECT TOP 3 … ORDER BY …

Update: предикат TOP отсутствует в данном диалекте — см. замечание коллеги El Sanchez ниже.

Цитата:

Цитата Iska

Не надо помещать. »

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

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

Цитата:

Цитата Iska

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

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

Цитата:

Цитата Iska

я вообще охренел, когда увидел * и просто имена полей в выборке с группировкой »

― Что это у тебя?
― Надо!
(из к/ф "Джентельмены удачи")

Цитата:

Цитата Iska

Кстати, какая строка из нескольких, попавших в группировку, в этом случае берётся для таких полей? »

Iska, здесь 2 уровня вложенности SELECT. Во 2-ом выбираются строки не старше 100 дней и сортируются по убыванию сначала по количеству логонов, а потом по штампу времени логона. Если здесь делать группировку по пользователю, то от сортировки будет мало толку, в результат попадёт первая строка из группы неотсортированных строк, поэтому отсортированные строки поступают в SELECT 1-го уровня. Так как строки уже отсортированы при группировке первыми строками в группах будут строки с ближайшим штампом времени, результат сортируется по убыванию по количеству логонов, а затем ограничивается до 3 строк. Внешний SELECT формирует окончательный результат.

El Sanchez, нет, я об общем принципе, который видел в соседней теме. Например:

Код:

Field1        Field2        Field3

АААА        ЯЯЯЯ        Группа1

ББББ        ЮЮЮЮ        Группа2

ВВВВ        ЭЭЭЭ        Группа1

ГГГГ        ФФФФ        Группа2

ДДДД        ХХХХ        Группа2

Запрос:

Код:

SELECT *

FROM MyTable

GROUP BY Field3

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

Цитата:

Цитата Iska

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

Iska, АААА/ЯЯЯЯ для Группа1 и ББББ/ЮЮЮЮ для Группа2, т.к. это первые найденные строки при формировании групп.

Цитата:

Цитата Iska

я об общем принципе, который видел в соседней теме. »

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

Цитата:

Цитата El Sanchez

Griboed0ff »

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

Цитата:

Цитата Iska

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

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

Цитата:

Цитата Griboed0ff

я пока не умею экспортировать данные запроса сразу в таблицу csv. »

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

Цитата:

Цитата El Sanchez

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

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

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

Цитата:

Цитата Griboed0ff

у меня информация в таблице с множеством разных пк и получить информацию нужно о всех, что содержаться в таблице, а не только для "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

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