Задачи с БД

1) Напишите код на GoLang с использованием PostgreSQL и оконных функций для того, чтобы найти для каждого клиента третий по величине заказ, сами придумайте структуру таблиц.

Для решения этой задачи сначала создадим структуру таблиц в PostgreSQL, а затем напишем код на GoLang для выполнения запроса с использованием оконных функций.

Шаг 1: Структура таблицы в PostgreSQL

Допустим, у нас есть таблица orders, которая содержит информацию о заказах клиентов. Вот пример структуры таблицы:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    client_id INT NOT NULL,
    order_value DECIMAL(10, 2) NOT NULL,
    order_date DATE NOT NULL
);

Шаг 2: SQL запрос с оконной функцией

Мы будем использовать оконную функцию ROW_NUMBER() для определения третьего по величине заказа для каждого клиента. Запрос будет выглядеть следующим образом:

SELECT client_id, order_value
FROM (
    SELECT client_id, order_value,
           ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY order_value DESC) as rn
    FROM orders
) as ranked_orders
WHERE rn = 3;

Шаг 3: Код на GoLang

Теперь напишем код на GoLang, который будет выполнять этот SQL-запрос и выводить результаты. Для работы с PostgreSQL мы будем использовать пакет github.com/lib/pq.

package main

import (
    "database/sql"
    "fmt"
    "log"

    _ "github.com/lib/pq"
)

const (
    host     = "localhost"
    port     = 5432
    user     = "yourusername"
    password = "yourpassword"
    dbname   = "yourdbname"
)

func main() {
    // Подключение к базе данных
    psqlInfo := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable",
        host, port, user, password, dbname)
    db, err := sql.Open("postgres", psqlInfo)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Проверка соединения
    err = db.Ping()
    if err != nil {
        log.Fatal(err)
    }

    fmt.Println("Successfully connected!")

    // Выполнение запроса
    rows, err := db.Query(`
        SELECT client_id, order_value
        FROM (
            SELECT client_id, order_value,
                   ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY order_value DESC) as rn
            FROM orders
        ) as ranked_orders
        WHERE rn = 3;
    `)
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()

    // Вывод результатов
    for rows.Next() {
        var client_id int
        var order_value float64
        err = rows.Scan(&client_id, &order_value)
        if err != nil {
            log.Fatal(err)
        }
        fmt.Printf("Client ID: %d, Third Largest Order Value: %.2f\n", client_id, order_value)
    }

    // Проверка ошибок при итерации
    err = rows.Err()
    if err != nil {
        log.Fatal(err)
    }
}

Этот код подключается к базе данных PostgreSQL, выполняет SQL-запрос с оконной функцией и выводит результаты. Убедитесь, что вы заменили параметры подключения к базе данных на свои собственные.

2) Есть база данных с такой схемой данных:

/ user
id | firstname | lastname | birth
1 | Ivan | Petrov | 1996-05-01
2 | Anna | Petrova | 1999-06-01
3 | Anna | Petrova | 1990-10-02

// purchase
sku| price | user_id | date
1 | 5500 | 1 | 2021-01-16
1 | 5700 | 17 | 2021-01-15
2 | 4000 | 1 | 2021-02-14
3 | 6000 | 2 | 2021-03-11
4 | 400 | 2 | 2021-03-02

// ban_list
user_id | date_from
1 | 2021-23-08

Необходимо составить SQL-запрос, чтобы выбрать уникальные комбинации пользователя и id товара (sku), для всех покупок, совершенных пользователем до того, как его забанили. Отсортировать сначала по имени пользователя, потом по SKU

Для выполнения этой задачи нам нужно составить SQL-запрос, который будет:

1. Объединять таблицы user и purchase по user_id.

2. Исключать покупки, совершенные после даты бана пользователя.

3. Выбирать уникальные комбинации пользователя и SKU.

4. Сортировать результаты по имени пользователя и SKU.

SQL-запрос

SELECT DISTINCT u.id, u.firstname, u.lastname, p.sku
FROM user u
JOIN purchase p ON u.id = p.user_id
LEFT JOIN ban_list b ON u.id = b.user_id
WHERE b.date_from IS NULL OR p.date < b.date_from
ORDER BY u.firstname, p.sku;

Объяснение запроса

1. Объединение таблиц:

   FROM user u
   JOIN purchase p ON u.id = p.user_id
   LEFT JOIN ban_list b ON u.id = b.user_id

   Мы объединяем таблицы user и purchase по user_id, а также делаем левое соединение с таблицей ban_list, чтобы получить информацию о бане пользователя, если она существует.

2. Фильтрация покупок:

   WHERE b.date_from IS NULL OR p.date < b.date_from

   Мы исключаем покупки, совершенные после даты бана. Если пользователь не забанен (т.е. b.date_from IS NULL), то все его покупки включаются в результат. Если пользователь забанен, то включаются только те покупки, которые были совершены до даты бана.

3. Выбор уникальных комбинаций:

   SELECT DISTINCT u.id, u.firstname, u.lastname, p.sku

   Мы выбираем уникальные комбинации пользователя и SKU, используя DISTINCT.

4. Сортировка результатов:

   ORDER BY u.firstname, p.sku;

   Мы сортируем результаты сначала по имени пользователя (u.firstname), а затем по SKU (p.sku).

Пример результата

Для приведенных данных результат запроса может выглядеть следующим образом:

id | firstname | lastname | sku
2  | Anna      | Petrova  | 3
2  | Anna      | Petrova  | 4
1  | Ivan      | Petrov   | 1
1  | Ivan      | Petrov   | 2

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