Создание таблиц в SQL
Всем привет! Сегодня раскрою тему по работе с таблицами в SQL.

При создании таблицы есть довольно много параметров, которые можно использовать. Так что можно идти двумя путями:

1️⃣ Прописываем типы переменных, указываем PRIMARY KEY, правила сортировки, ограничения на нулевые значения и многое другое ...

Используем в случае:
🟡 Когда нужно сэкономить место в базе
🟡 Для ускорения работы скрипта
🟡 Для создания постоянной таблицы для общего пользования

2️⃣ Используем минимальный синтаксис для создания, не паримся с указанием типов данных и тратим на это минимум нашего драгоценного времени 👌🏼

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

Примеры по обоим вариантам смотри на картинке выше.

Ставь реакции, если было полезно и предлагай свои темы для разбора! 👀

#sql

🌟 Используем AI для написания скриптов 🌟

Всем привет! ✌🏼

Почти каждый уже попробовал искусственный интеллект для создания картинок/ текста/ собственных аваторов и т.п. Ну и написание скриптов с помощью искусственного интеллекта уже тоже не новость. Поэтому предлагаю в этом и последующих постах оценить насколько хорошо AI будет генерировать нам скрипты разной сложности. 👾

Сегодня рассмотрим perplexity.ai

1️⃣ Быстро справился с простым запросом "Напиши простой скрипт на postgresql" 👌 :

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100)
);

2️⃣ Не видит проблем с написанием объединения "Напиши скрипт с объединением таблиц на postgresql"🙂:

SELECT orders.order_id, orders.order_date, customers.customer_name
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id;

3️⃣ Отлично справился с написанием запроса с объединением + применением OVER и CASE + обобщенными табличными выражениями 😍

WITH ranked_orders AS (
    SELECT
        order_id,
        customer_id,
        order_date,
        total_amount,
        RANK() OVER (PARTITION BY customer_id ORDER BY order_date) AS order_rank
    FROM orders
),
customer_info AS (
    SELECT
        customer_id,
        customer_name,
        country
    FROM customers
)
SELECT
    r.order_id,
    r.customer_id,
    c.customer_name,
    c.country,
    r.order_date,
    r.total_amount,
    CASE
        WHEN r.order_rank = 1 THEN 'First Order'
        WHEN r.order_rank = 2 THEN 'Second Order'
        ELSE 'Multiple Orders'
    END AS order_rank_description
FROM ranked_orders r
JOIN customer_info c ON r.customer_id = c.customer_id;

4️⃣ Попросила исправить ошибку в скрипте - готово ✔️
Код с простой ошибкой:

select id, check_num, value
from info
having value = 5

Ответ ИИ:

Ошибка в данном скрипте заключается в том, что оператор HAVING используется неправильно. Оператор HAVING обычно используется с агрегатными функциями (например, SUM, COUNT, AVG) для фильтрации результатов группировки, а не для фильтрации отдельных строк данных. Для фильтрации строк данных в SQL используется оператор WHERE.
Исправленный скрипт будет выглядеть следующим образом:

SELECT id, check_num, value
FROM info
WHERE value = 5;

5️⃣ Усложняем запрос - просим написать скрипт для подключения к базе данных в программе Dbeaver на Python:

import pyodbc

# Установка параметров подключения
server = 'your_server'
database = 'your_database'
username = 'your_username'
password = 'your_password'

# Строка подключения
conn_str = f'DRIVER={{ODBC Driver 17 for SQL Server}};SERVER={server};DATABASE={database};UID={username};PWD={password}'

# Подключение к базе данных
conn = pyodbc.connect(conn_str)

# Создание курсора для выполнения SQL запросов
cursor = conn.cursor()

# Пример выполнения SQL запроса
cursor.execute("SELECT * FROM your_table")
rows = cursor.fetchall()

for row in rows:
    print(row)

# Закрытие соединения
conn.close()

Итог:
🟢 Можно пробовать использовать для помощи в написании скриптов
🟢 Можно запросить разъяснения к какой ни будь теме по SQL - базу выдаст хорошую!
🟢 ИИ не только выплевывает скрипт, но и пишет пояснения для него!
🟢 Можно группировать тематики своих запросов для удобства, плюсом сохраняет историю старых запросов

На мой взгляд довольно удобно! Ошибок не обнаружено, но, кто знает, может не все так идеально 😤
Ссылка на ИИ: https://www.perplexity.ai/

#ИИ #sql

Хэлоу 🙃

Решила немного раскрыть пост про рабочие программы, поэтому рассказываю как установить Dbeaver и настроить рабочее окружение .

Для этого понадобится установить PostgreSQL и саму программу Dbeaver:
- Ссылка для скачивания PostgreSQL (выбираем под свою ОС)
- Ссылка для скачивания Dbeaver (выбираем DBeaver Community 24.0.1 - installer)

Первым шагом устанавливаем PostgreSQL:
🟢Во время установки указываем данные для localhost (можно оставить все по дефолту и указать только пароль)
🟢Пароль запоминаем, так как он понадобится при настройке Dbeaver:

host: localhost
port: 5432
user: postgres
password: <любой пароль>

Вторым шагом ставим Dbeaver:
🟢После установки запускам и создаем новое соединение (1) (значок вилки 🔌 в левом верхнем углу)
🟢Выбираем PostgreSQL
🟢Указываем пароль из первого пункта и жмем "Готово"
🟢В левом блоке должно отразиться строка (2) postgres c нашим localhost:5432
🟢Раскрываем список до "Таблицы" (3) через правую кнопку мыши выбираем "Импорт данных"
🟢Выбираем наш файл (у меня данные по покупателям магазина) и загружаем в базу
🟢Выбираем Postgres (4)
🟢Делаем первый запрос (5)
🟢Радуемся и идем пить чай ✨

С этими программами уже можно начинать писать свои первые SQL - скрипты и набираться реального опыта!
Подписывайся и жди будущие посты, в которых я расскажу о реальных задачах, которые можно выполнить на данных по транзакциям ✔️

#sql #стек

🌟Используем ChatGPT 4.0 для написания скриптов 🌟

Продолжаю прошлый пост про AI для решения простых аналитических задачек. В прошлый раз пробовала силы Preplexity , результат был хорошим.

В этот раз посмотрю, как самый популярный AI ответит на вопросы из предыдущего поста + подкину пару новых задачек ✔️

В прошлый раз я задала ИИ 3 вопроса:
🟡Напиши простой скрипт на postgresq
🟡Напиши скрипт с объединением таблиц на postgresql
🟡Напиши запрос с объединением + применением OVER и CASE + обобщенными табличными выражениями

С этими же вопросами ChatGPT 4.0 справился так же отлично, кроме того добавил даже то, что не просили ✔️
Каждый раз добавлял часть скрипта с созданием таблицы, с внесением в нее данных. Полезно, но немного чрезмерно. Свой код также пояснил комментариями.

В этот раз попросила сгенерить данные, которые смогу потом использовать для тестов.

Запрос звучал так:

Создай таблицу в postgresql из 5-и столбцов (check id; customer id; date; value; cashback).
Внеси 100 строк рандомных данных.
check id - только уникальные значения.
customer id могут повторяться.
date - формат даты со временем, данные не повторяются, данные в диапазоне от 2024-01-01 до 2024-02-01.
value - не могут быть менее 1 и более 100000.
cashback - не более 2 % от value.

Первый скрипт от ИИ:

-- Удаляем таблицу, если она существует
DROP TABLE IF EXISTS transactions;

-- Создание таблицы
CREATE TABLE transactions (
    check_id SERIAL PRIMARY KEY,
    customer_id INTEGER,
    transaction_date TIMESTAMP,
    value NUMERIC(10, 2),
    cashback NUMERIC(10, 2)
);

-- Вставка случайных данных
INSERT INTO transactions (customer_id, transaction_date, value, cashback)
SELECT
    (random() * 20)::INTEGER + 1,  -- customer_id от 1 до 20
    timestamp '2024-01-01 00:00:00' + random() * (timestamp '2024-02-01 23:59:59' - timestamp '2024-01-01 00:00:00'),  -- случайная дата и время
    (random() * 99999 + 1)::NUMERIC(10, 2),  -- value от 1 до 100000
    ((random() * 2 / 100) * (random() * 99999 + 1))::NUMERIC(10, 2)  -- cashback не более 2% от value
FROM generate_series(1, 100);

Сразу видим проблемы:
Несерьезная - не так назвал столбец 'date' ☹️
Серьезная - cashback формируется рандомно, привязки к value нет, поэтому значения могут быть больше, чем 2 %.

Пришлось дважды указывать в чате на ошибку перед финальным результатом.

INSERT INTO transactions (customer_id, date, value, cashback)
SELECT
    customer_id,
    date,
    value,
    (value * (random() * 0.02))::NUMERIC(10, 2) AS cashback
FROM (
    SELECT
        (random() * 20)::INTEGER + 1 AS customer_id,  -- customer_id от 1 до 20
        timestamp '2024-01-01 00:00:00' + random() * (timestamp '2024-02-01 23:59:59' - timestamp '2024-01-01 00:00:00') AS date,  -- случайная дата и время
        (random() * 99999 + 1)::NUMERIC(10, 2) AS value  -- value от 1 до 100000
    FROM generate_series(1, 100)
) AS subquery;

С таким запросом уже получаем нужный нам набор данных, которые можем использовать для тестов, на собесах и для собственного обучения 🔥

Итог:
🟡С легкими запросами - все так же ОК
🟡ChatGPT очень любит все досконально объяснять
🟡Нужно проверять AI на многоступенчатых запросах - вдруг какой-то пункт он все таки не учел и скрипт нужно дорабатывать
🟡Использование AI без общего понимания происходящего может быть бесполезным 🙂

Ссылка на ChatGPT

#SQL #ИИ

Привет 😉
Информация для тех, кто хочет более уверенно писать на SQL.

Советую попробовать сайт SQLBolt. Тут собраны небольшие уроки с описанием методов, функций и прочими прелестями по SQL. В конце каждого урока есть несколько заданий на составление запросов, которые можно выполнить прямо на странице.

✔️ Сайт бесплатный
✔️ Не нужно регистрироваться
✔️ Удобный интерфейс
✔️ Запросы выполняются по мере написания
✔️ Если не получается решить - можно посмотреть финальное решение

✖️ На английском (спокойно обходится расширением браузера с переводчиком, но также переводит и слова в запросе )
✖️ Ошибки в запросе не подсказывает

#SQL

❓Как не потерять дни при работе с датами в SQL❓

Скажу сразу - я теряла ... и не раз ...
Но те времена давно миновали, стоило только разобрать простую разницу между операторами 🙂

Написала небольшой скрипт (на Postgresql) со сгенерированными данными и операторами для примера.
Качай выше для наглядности 👆

1️⃣ BETWEEN
Включает в себя все границы диапазона, т.е. можно представить в виде >= AND <=.
НО есть нюанс с форматом timestamp и datetime -> если время не указано, то оно автоматом воспринимается как 00:00:00 , что приводит к сокращению границы с правой стороны.

2️⃣ ОПЕРАТОРЫ СРАВНЕНИЯ > и <
Тут граница будет еще уже, чем в предыдущем случае.
Потеря даты наоборот может произойти с левой стороны, опять же из-за форматов timestamp и datetime.
Если привести формат столбца к date, то мы как раз потеряем первый день, так как сравнение происходит не по дате и времени, а только по дате.

Ошибка простая, а результат может сильно навредить ☹️

#SQL

⚡️ Ускоряем SQL-запросы ⚡️

На прошлой неделе столкнулась с тем, что часть моих запросов обрабатывается КАТАСТРОФИЧЕСКИ долго ☹️

Пришлось исправлять и оптимизировать скрипты, чтобы не сидеть в ожидании часами.
Как результат - собрала несколько простых правил для сокращения времени выполнения ваших запросов:

✔️ Выбираем только нужные столбца, а не все имеющиеся в таблице
✔️ Используем LIMIT, если необходимо только проверить верность выполнения промежуточного запроса
✔️ Если есть возможность ограничить данные через WHERE - активно пользуемся!
✔️ Вместо подзапросов выгоднее использовать СТЕ, а вместо СТЕ - временные таблицы
✔️ Оптимально выбираем JOIN. INNER JOIN будет работать быстрее, чем LEFT/RIGHT/OUTER JOIN
✔️ Создаем и используем индексы для тех столбцов, которые часто используем в JOIN/WHERE/OREDER BY

Для тех, кому интересно посмотреть на предполагаемое время обработки блоков - функция EXPLAIN в помощь ✨

#sql

MySQL ⚡️VS⚡️ PostgreSQL

На работе скачу между двумя синтаксисами SQL, так как на разных проектах развернуты разные базы данных.

Пару лет писала только на PostgreSQL, поэтому, когда забываюсь, вплывают ошибки, так как пишу на неподходящем синтаксисе 🙃

Расскажу о трех различиях, с которыми столкнулась при написании скриптов:

1️⃣ Изменение типов данных
В PostgreSQL можно использовать двойное двоеточие, а в MySQL - CAST

-- Для PostgreSQL
SELECT column_name::integer FROM table_name;

-- Для MySQL
SELECT CAST(column_name AS UNSIGNED) FROM table_name;

2️⃣ Работа с данными и временем

В PostgreSQL можно использовать CURRENT_DATE, CURRENT_TIME, и EXTRACT.
У MySQL - CURDATE(), CURTIME(), и DATE_FORMAT()

3️⃣ Создание и объединение таблиц

PostgreSQL поддерживает довольно большое количество функций, например - INTERSECT, EXCEPT
MySQL не поддерживает INTERSECT и OUTER JOIN

Как итог - синтаксисы SQL практически совпадают, но есть нюансы 🙂
Если пишешь на любом из них, то перейти на другой - уже не проблема.

Поэтому можно не париться, если вы учились на Postgres, а а вакансиях усердно пишут MySQL ✔️

#SQL

🔄 Переделываем чужие скрипты 🔄

Периодически заглядываю на stack Overflow и на днях увидела вопрос по SQL с конструкцией CASE 👇

select 
    id,
    date,
    case when score > 500 then 'A'
         when score <= 500 and score > 200 then 'B'
         when score <= 200 and score > 1000 then 'C'
         when score <= 100 and score >= 0 then 'D'
    end as score_type
from table

Запрос выглядит довольно массивным, хотя можно внести пару правок и все будет смотреться лучше 🙂

Что упрощаем:
1️⃣ Убираем лишние условия в конструкции, дублирующие логику предыдущих условий. Например score <= 500 во втором отборе при наличии score > 500 в первом. Это лишь повторит логику предыдущего условия, но никак не повлияет на отбор.

2️⃣ Последнее условие меняем на else (если точно уверены, что у нас не осталось других возможных вариантов). Это позволит скрипту работать чуть быстрее, так как последнюю проверку он не будет проводить.

3️⃣ В самый верх ставим те условия, которые будут встречаться чаще, чем остальные. Это перевернет нашу конструкцию и сократит количество вычислений, так как для определения самых частых вариантов нам потребуется только одна проверка.

После изменений скрипт становится таким👇

select 
    id,
    date,
    case when score <= 100 then 'D'
         when score <= 200 then 'C'
         when score <= 500 then 'B'
         else  'A'
    end as score_type
from table

Как итог:
Результат не изменён, скорость выполнения при больших объемах сократится, выглядит симпатичней 💙 Берем на заметку для своих конструкций ❗️

#sql

Не умеешь красиво оформлять SQL-запросы - тебе сюда 👇

Наткнулась на сайт, где из любого SQL-запроса автоматически можно сделать конфетку.
Скрипт приводится к общепринятому формату, который легко читать и не стыдно вывести в люди 🔥

Пригодится для оформления сркипта по тестовому заданию 🔜 CodeBeautify

#SQL

"Палки" в SQL-скрипте - это норма 👍

Первый раз столкнулась с объединением слов на прошлом месте работы, когда читала чужой код и первый раз увидела || в скрипте. Тогда немного выпала, так как не была готова к такому лаконичному решению 🙃

На мой взгляд, объединение слов - явление редкое, но чем больше знаешь, тем лучше 🌟

Решила собрать основные способы 👇

1️⃣ Самое простое - оператор ||

SELECT first_name || ' ' || last_name AS full_name
FROM users;

2️⃣ Обычный CONCAT

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM users;

3️⃣ CONCAT_WS с выбором разделителя

SELECT CONCAT_WS(' ', first_name, middle_name, last_name) AS full_name
FROM users;

4️⃣ Объединение с использованием COALESCE для избавления от NULL

SELECT CONCAT(COALESCE(first_name, ''), ' ', COALESCE(last_name, '')) AS full_name
FROM users;

Все способы подходят для PostgreSQL и приведут к одному результату, так что выбор только за вами и за нюансами задачи 🤪

#SQL

Первое правило курсов по аналитике - никому не рассказывай о своих SQL-скриптах 👇

Во время обучения на аналитика все проходят через решение большого количества SQL-запросов. В начале это простые select-ы с limit или с where, но под конец блока по SQL запросы становятся достаточно массивными.

По неопытности мне казалось, что это чуть ли не вершина успеха ⭐️, так как в одном запросе могли присутствовать и СТЕ и оконные функции.

Выглядит впечатляюще, думала я 🔜 самое время делиться моими супер скриптами с миром 🔜 кидаю это все безобразие в свой git-hub.

Так к концу курса мое портфолио дополнилось примерно десятком разнообразных проектов по SQL. Между собой они связаны не были, и каждый из них решал СУПЕР УЗКУЮ задачу.

В этом и была моя главная ошибка - я посчитала, что наличие этих небольших скриптов в моем портфолио - хороший шаг. Но проблема в том, что для бизнесовых задач эти скрипты - ВООБЩЕ ни о чем не говорят.

Да, на твой гит может заскочить аналитик, который принимает участие в твоем собеседовании и он по ним поймет - что ты умеешь делать, а что нет.

Вот только на рынке требования к SQL довольно похожи и такими работами с курсов никого не удивишь, ведь ты и так это должен уметь делать 🙃

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

Ставь ⚡️ - расскажу какой пет-проект будет лучшим решением (с применением SQL)
Ставь 🔥 - расскажу про портфолио в GIT

#SQL #курсы

Какой пет-проект будет лучшим решением для твоего портфолио❓

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

Какие цели будем преследовать в погоне за хорошим пет-проектом❓

🟢 Отражение существующих навыков
🟢 Решение конкретной задачи
🟢 Наличие результата (рабочий скрипт, выводы исследования)

Все эти пункты - must have, но есть еще один, который будет попадать в сердце нанимающей стороны:

🟢 Тема и направление проекта

Простыми словами:

Хочешь попасть в маркетинговую компанию 🔜 делай исследование по рекламе/ опросам

Интересен ритейл 🔜 тебе поможет анализ данных крупных магазинов, подсчет метрик по временным отрезкам, Retention

Мечтаешь работать с мобильными приложениями 🔜 считай конверсии, меры центральной тенденции по времени нахождения пользователя в приложении

Думаю, все уже догадались, что гораздо удобнее, когда нравится одно направление 🙂
В таком случае можно глубже погрузиться в конкретную тему.

Где берем данные ❓

В открытом доступе огромное количество датасетов. Надо только потратить время на поиск того, который подойдет больше других. Как пример - ищем на Kaggle, Github.

Какой проект подойдет всем ❓

Рассмотрим простой проект, для которого понадобится только SQL.
Для этого подбираем себе датасет с покупками. Оффлайн или онлайн - не важно, так как транзакции есть в любом бизнесе.

Что важно - так это длительность сбора данных. Идеально, если в датасете собраны данные за 3 месяца и больше.

Также обращаем внимание на то, чтобы id пользователей повторялись (чтобы были пользователи, которые совершили транзакцию более 1 раза).

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

Цель проекта - посчитать Retention Rate по когортам. В качестве когорт будут выступать номера недель/месяцев, в которые пользователи совершили свою первую покупку.
Retention можно привязать как к посещениям магазина так и к доходу.

Для завершения делаем выводы и собираем таблицу с процентами (пример ниже 👇).

В результате - у нас выполнена классическая задача продуктового аналитика 🙂

Ставь 🔥 - если пост был полезен
Ставь ⚡️ - если остались вопросы (жду в комментариях)

#пет_проект #SQL

Тренируем свой SQL тут 👇

Подборка тренажеров для тех, кто уже немного может в SQL и хочет оттачивать свои навыки:

🔘 Великий Leetcode, куда без него

🔘 19 уроков от SQL Bolt

🔘 120 + заданий разной сложности от SQL Academy

🔘 Задачи отсортированные по темам от PostgreSQL Exercises

🔘 Разнообразные задачи от HackerRank

🔘 Как-то давно советовала Симулятор SQL от Karpov Courses (на нем отрабатывала оконные функции)

Все тренажеры отлично подойдут для начинающих. Можно начать с легких уровней или выбирать для себя проблемные темы и решать только их.

#sql #обучение

Практикуем харды 🎧

Наткнулась на платформу с вопросами с собесов - StrataScratch - подходит для всех, кто работает с данными ❤️

Преимущества:

🔘У каждого вопроса указана компания, которая их задает
🔘Есть разбивка на языки программирования (SQL, Python) + можно выбрать индустрию, топик и сложность
🔘На бесплатном тарифе вопросов и задач - много, так что не обязательно брать подписку
🔘Есть ответы, так что можно всегда их подсмотреть (кроме премиума)
🔘Есть не только вопросы с кодингом, но и без него! На мой взгляд - практики по таким вопросам часто не хватает на реальных собесах
🔘Можно попрактиковаться над проектами с датасетами, но тут, насколько поняла, только с подпиской

Ограничения:

🔘Все на английском 🙂

Выглядит, как еще один способ бесплатно оттачивать свои навыки 🔥

#sql #обучение

*️⃣Навигация по каналу*️⃣

На прошлой неделе канал достиг отметки в 500 подписчиков!
Рада видеть всех, кто пришел недавно, и тех, кто уже давно заглянул на канал и остался ❤️

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

Несколько фактов обо мне:
🟣Залетела в аналитику после курса без профильного образования
🟣Сейчас работаю аналитиком на удаленке
🟣Родилась в Чите, а живу пока в Тбилиси
🟣Занимаюсь скалолазанием больше 10 лет 🧗🏼‍♀️

Личный опыт:
🟣Что бы я изменила в своем обучении аналитике
🟣Обратная связь, которая поменяла подход к собесам
🟣История моего джуновского резюме

Серия постов про этап поиска работы:
🟣Вакансий на всех не хватает
🟣Злые hr-ы не хотят меня замечать
🟣Пробиваемся к техническому собеседованию
🟣Боремся за место под солнцем на техническом собесе

Для консультации - пиши сюда 🙂

И всегда можно воспользоваться навигацией по хэштегам:
#собеседования #резюме и #поиск_работы - все про подготовку к собесам, прокачке резюме и про поиск соответственно
#обучение - полезные материалы
#курсы - ссылки на чаще всего бесплатные курсы по аналитике
#о_работе - примеры задачек с моей работы
#обо_мне - личные посты про путешествия, будни, взлеты и падения
#sql #статистика - посты по соответствующим темам
#стажировки - иногда нахожу оплачиваемые ближайшие стажировки
#аналитика_всего - что-то интересное и простенькое по аналитике
#пет_проект - советы и примеры по проектикам