Data Science. SQL hub

🔥 Дайджест полезных материалов из мира SQL за неделю

Почитать:
— Отправка и обработка HTTP запросов в PostgreSQL
— Airflow vs NiFi: исследуем оркестратор для формирования витрин данных
— Повышение эффективности SQL-запросов: советы и рекомендации
— Top 10 Tools For SQL Writing For Students
— How Randomized SQL Testing Can Help Detect Bugs?
— SQL Joins Infographic
— ACID and Transactions in SQL
— SQL - Stored Procedures for Reusable and Secure SQL Code
— SQLite Create Table If Not Exists: Conditional Table Creation
— Text2SQL: Converting Natural Language to SQL
— CRUD Operations in SQL
— Should you use an enum column or a table to store allowed values in SQL?
— 7 Tips to Optimise Postgres query performance

Посмотреть:
🌐 Chatgpt, Bard , Claude, Theb - используй API лучших нейросетей бесплатно и без ВПН на Python. (⏱ 05:23)
🌐 NLP практика. Определяем тональность текста при помощи NLTK и DL (⏱ 19:37)
🌐 Задача, которая очень часто встречается на собеседованиях #python (⏱ 00:59)
🌐 Хитрая задача с собеседования #Python (⏱ 00:59)
🌐 Решение непростой задачи с Leetcode, часто всплывает на собеседования #pythontutorial (⏱ 00:59)
🌐 Django шаблонизация и маршрутизация (⏱ 09:11)

Хорошего дня!

@sqlhub

🖥 Как заблокировать строки?

С помощью

SELECT ... FOR UPDATE

Если другая транзакция заблокировала какую-либо из этих строк, то она блокируется.

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

SELECT ... FOR UPDATE SKIP LOCKED

Это позволит обойти все строки, заблокированные другой транзакцией.

#SQL

@sqlhub

🖥 Выполнение логики IF/THEN в #SQL с помощью выражений CASE:

CASE
WHEN ... THEN ...
WHEN ... THEN ...
...
[ ELSE ... ]
END

• Simple => Проверяем, равен ли селектор значению
• Searched => Проверка любых условий

База данных обрабатывает их сверху вниз, возвращая первое истинное условие.

#SQL

@sqlhub

🖥Создание датасетов "на лету" в Oracle Database 23c с помощью конструктора значений таблицы

SELECT * FROM ( VALUES
( 'r1v1', 'r1v2' ), -- row1
( 'r2v1', 'r2v2' ), -- row2
--etc.
) tab_alias ( col1, co2l )

#SQL

@sqlhub

Вы можете объединить строки из двух таблиц в одну с помощью UNION и UNION ALL

В чем разница?

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

UNION ALL сохраняет все строки из каждой таблицы.

#SQL

@sqlhub

Хотите получить в #SQL строку top-N для каждой группы? Например.

Лучшие результаты/экзамен
Самые высокооплачиваемые сотрудники/отдел
Лучший результат/команда

Пронумеруйте строки для каждой группы в подзапросе с помощью функции

ROW_NUMBER () OVER (
PARTITION BY <group_cols> ORDER BY <sort_cols>
)

Затем отфильтруйте эти значения во внешнем запросе.

@sqlhub

🖥 Хотите сравнить две таблицы и вернуть разные строки с помощью #SQL?

Вот простой способ это сделать:

SELECT <cols>
FROM ( SELECT 'T1' T, T1.* FROM T1 UNION ALL SELECT 'T2' T, T2.* FROM T2 )
GROUP BY <cols>
ЕСЛИ COUNT (*) <> 2

@sqlhub

🖥 Хотите запретить втсавку NULL в столбец и использовать вместо него значение по умолчанию?

В Oracle Database используйте:

CREATE TABLE ... ( <col> <datatype> DEFAULT ON NULL <expr> )

Если при INSERT опустить <col> или поместить в него NULL, то база данных использует вместо него <expr> по умолчанию.

#sql #tips #junior

@sqlhub

🔥 Дайджест полезных материалов из мира SQL за неделю

Почитать:
— Регулярные выражения в реальных задачах
— О Prepare statements, серверном кэше скомпилированных запросов или как эффективно кэшировать запросы в YDB
— Exploring Database Options for Java Applications
— Fearless Multitenancy
— User-Defined Functions in SQL: Expanding Your Database Toolkit
— SQL Pro tips : AWS Athena SQL UNPIVOT : CROSS JOIN UNNEST
— SQL Pro tips : GCP BigQuery SQL CROSS JOIN with UNPIVOT UNNEST
— Exploring String Field Types in Databases: CHAR, VARCHAR, and TEXT
— PostgreSQL Internals: A Deep Dive into the Inner Workings of a Powerful Relational Database
— SQL - Optimize Your SQL Queries for Performance
— SQL - Use Indexes Wisely for Performance Optimization
— Performing SQL Commands

Посмотреть:
🌐 Разбор задачи с собеседования Data Science. Подготовка на практике (⏱ 26:12)
🌐 Cобеседование #Python топовый банк Goldman Sachs Associate на 12000 $ #задача #программирование (⏱ 01:00)
🌐 Facebook задача с собеседования на #Python на 120000 рублей #программирование #задача #код (⏱ 00:59)
🌐 Разбора задачи с собеседования #Golang (⏱ 00:22)
🌐 Django создание модели. Вывод даных на страницу (⏱ 13:01)

Хорошего дня!

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

Будем использовать две простые таблицы: компании companies и их вакансии jobs.

Есть три вымышленные компании — Hoogle, Emazon и Neta — которые предлагают на удивление мало вакансий:

Hoogle интересуется аналитиками данных. Emazon нанимает Go-разработчиков и ML-инженеров. У Neta нет вакансий. А какая-то ноунейм-компания с идентификатором 99 отчаянно разыскивает UI-дизайнера.

Время джойнить!

📌 Читать

@sqlhub

🖥 SQLFlow - отличный инструмент для визуализации SQL-запросов.

Легкий и простой интрумент для визуализация связей и структур БД, поддерживающий более 20 разных БД.


• Demo
• Github

@sqlhub

🖥 Новый курс по SQL от Гарвардского университета бесплатно!

Курс CS50 "Введение в базы данных с SQL" теперь входит в линейку CS50.

Откройте для себя возможности по анализу данных на SQL для запросов и управления данными в реляционных базах данных - БЕСПЛАТНО.

📌 Курс
📌 cs50

@sqlhub

🖥 Z Order в #DeltaLake организует данные в хранилище для повышения производительности запросов.

В данном примере запрос должен просканировать 8 отдельных файлов, чтобы найти строки, в которых id = 5. Однако при оптимизации Z Order запрос должен просканировать только один файл, чтобы найти нужные строки.

• Delta Lake Z Order
• Что такое Delta Lake

@sqlhub

🖥 Оптимизация хранения данных в СУБД Greenplum

Greenplum — это open source MPP СУБД, основанная на PostgreSQL.

Архитектура СУБД укрупненно представляет собой систему, состоящую из n-го количества Segment Host — процессов/серверов, на которых производится хранение и обработка данных, и одного Master Host – процесса/сервера, являющегося точкой входа клиента, который также хранит внутри себя метаданные таблиц и распределяет обработку данных между сегментами.

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

1. Таблица с пользователями маркетплейса (более 4 млн.);
2. Корзина товаров (более 150 млн.);
3. Покупки пользователей в маркетплейсе (более 40 млн.);
4. Таблица с начислениями бонусов за покупки (более 20 млн.)
5. И т.д. (суммарно еще около 5-10 млн. строк уходят на перечень продаваемых товаров, различные справочники и другую сопутствующую информацию)

Аналитические запросы, в особенности с объединением этих таблиц, занимают достаточно большое количество времени и ресурсов, что негативно складывается на работе СУБД, коллег и меня в частности.

В связи с этим, оптимизация хранения таблиц является одним из основных вариантов оптимизации запросов (вкупе с объяснением того, как эти оптимальные запросы строить, но это уже совсем другая история).

Рассмотрим задачу выбора оптимального способа хранения данных подробнее. 👇

Читать

@sqlhub

🐍Как Snowflake повышает эффективность dbt-моделей на Python

dbt — это инструмент, помогающий управлять всеми трансформациями данных, которые выполняются в базах данных с использованием SQL.

Допустим, у вас есть таблица с общим числом клиентских заказов, и нужно подсчитать, сколько из них пришлось на праздничные дни. Выполнить эту задачу можно с помощью двух SQL-запросов.

1. Добавить столбец is_holiday в таблицу sf100_orders, объединив ее с all_holidays. Назвать полученную таблицу sf100_orders_annotated.
2. Подсчитать строки, для которых is_holiday=true в таблице sf100_orders_annotated.

Эти два шага объединяют исходные данные, визуализированные с помощью dbt: в изображении.

Производный граф: из двух исходных таблиц составляется таблица count_holidays

Используя dbt, можно записать эти две трансформации в виде “dbt-моделей”, которые представляют собой файлы, содержащие SQL и некоторую конфигурацию dbt (при необходимости):
-- sf100_orders_annotated.sql
{{ config(materialized='table') }}

select a.*, b.date is not null is_holiday
from {{ref('sf100_orders')}} a
left join {{ref('all_holidays')}} b
on a.o_orderdate = b.date
-- count_holidays.sql
{{config(materialized='table')}}

select count(*) total_days
, count_if(is_holiday) holiday_count
, holiday_count/total_days ratio
from {{ref('sf100_orders_annotated')}}

Эти два файла выглядят как типичный SQL, но с некоторым отличием. Вместо ссылок на таблицы dbt позволяет ссылаться на другие “модели” с помощью аннотаций {{ref(‘sf100_orders_annotated’)}}. Таким образом, dbt определяет связь и зависимости между всеми трансформациями, через которые проходят данные.

📌 Продолжение

@sqlhub

🖥 Интерактивная SQL шпаргалка

@sqlhub

🔥 Дайджест полезных материалов из мира SQL за неделю

Почитать:
— Небольшой тест нескольких БД при работе с многими таблицами
— Визуализация распределений вероятностей в Python.
— 15 лучших бесплатных курсов по Linux
— Погружаемся в базы данных и SQL: полезные материалы и инструменты от сотрудников Selectel
— Optimizing SQL Performance with Execution Plans
— Data Analysis with SQL
— Dominando la Preparación de Datos para Análisis: Funciones y Técnicas Esenciales
— Autoscout24 SQL Analysis
— SQL Pro tips : Oracle SQL CROSS JOIN with UNPIVOT
— SQL Pro tips : Oracle XE Quick Start
— Leveraging Apache Age for Advanced Data Analytics
— Choosing an OLAP Engine for Financial Risk Management: What to Consider?
— SQL Window Functions
— Parallels and MSSQL Server

Посмотреть:
🌐 Django начало работы. (⏱ 08:18)
🌐 Go - SQL Databases in Golang with the database/sql package
🌐 Популярная задача с собеседования в Яндекс #python #задача #программирование #собеседование (⏱ 01:00)
🌐 Новые функции в IOS 17 (⏱ 00:29)
🌐 TokenFlow редактирование видео с помощью текстового описания (⏱ 00:22)
🌐 Парсер телеграм каналов на Python. Гайд по написанию мощного бота. (⏱ 11:58)
🌐 Задача из Собеседования в Яндекс с зарплатой 85000 рублей #python #собеседование #задача #yandex (⏱ 00:59)
🌐 Асинхронный парсинг сайтов на Python (⏱ 22:06)

Хорошего дня!

@sqlhub

🖥 Pipelined Relational Query Language (PRQL)

PRQL - это современный язык для преобразования данных, который меняет обращение к базе данных на логически выстроенный пайплайн (конвейер).
Как и SQL, он легко читается, является явным и декларативным. Но в отличие от SQL, он формирует логический пайплайн преобразований и поддерживает такие абстракции, как переменные и функции. Он может использоваться с любой базой данных, использующей SQL.

from tracks
filter artist == "Bob Marley" # Each line transforms the previous result
aggregate { # `aggregate` reduces each column to a value
plays = sum plays,
longest = max length,
shortest = min length, # Trailing commas are allowed
}

• Github

@sqlhub

Sqlparse: парсинг SQL-операторов

Sqlparse - это невалидирующий парсер SQL для Python. Он обеспечивает поддержку разбора, разбиения и форматирования SQL-операторов.

$ pip install sqlparse
import sqlparse

📌 Файл с примерам кода и документацией по работе в закрепе.


raw = 'select * from foo; select * from bar;'
statements = sqlparse.split(raw)
statements
['select * from foo;', 'select * from bar;']


first = statements[0]
print(sqlparse.format(first, reindent=True, keyword_case='upper'))
SELECT *
FROM foo;

>>> # Parsing a SQL statement:
parsed = sqlparse.parse('select * from foo')[0]
parsed.tokens
[<DML 'select' at 0x7f22c5e15368>, <Whitespace ' ' at 0x7f22c5e153b0>, <Wildcard '*' … ]


@sqlhub

🖥 🖥SQLiDetector

Python-скрипт, позволяющий обнаружить SQL-инъекции путем отправки нескольких запросов и проверки regex-шаблонами для различных баз данных.

• Github

@sqlhub