count(), F.count(), F.countDistinct(), distinct()

В спарке есть несколько функций count, которые меня вначале путали, и тут я хочу расставить все точки над i.

1️⃣ count() - это действие
Оно запустит весь пайплайн расчетов и выведет на экран одно число - количество строк в датафрейме:

df.count()

Также можно использовать в агрегации, но нельзя поменять название столбца (будет count):

df.groupBy('category')
  .count()
  .orderBy('count')
  .show()

2️⃣ F.count() - это трансформация, агрегирующая функция

df.select(F.count('id')).show()

В отличие от первого способа в агрегации можно поменять название столбца:

df.groupBy('category')
  .agg(F.count('id').alias('cnt'))
  .orderBy('cnt')
  .show()

Отличия count() и F.count():

# результат - датафрейм с одной ячейкой
df1.select(F.count('id')).show()
# +---------+
# |count(id)|
# +---------+
# |        3|
# +---------+

# результат - число
df1.select('id').count()
# 3

3️⃣ F.countDistinct() - тоже агрегирующая функция

df.select(F.countDistinct('id')).show()

Тут вариант агрегации только один, потому что df.countDistinct() не существует:

df.groupBy('category')
  .agg(F.countDistinct('id'))
  .show()

При этом в спарке есть еще функция distinct(), которая тоже вносит некую путаницу.

Отличия distinct().count() и F.countDistinct():

# результат - датафрейм с одной ячейкой
df1.select(F.countDistinct('id')).show()
# +------------------+
# |count(DISTINCT id)|
# +------------------+
# |                 2|
# +------------------+

# результат - число
df1.select('id').distinct().count()
# 2

#spark

Как из деления int/int получить double?

🤓Вам нужно что-то посчитать: поделить одно количество на другое и округлить результат до 2х знаков после запятой.

Так как количество - это целое число, если написать просто count1/count2, то в результате мы получим тоже целое число: 15/26 = 0. А мы хотим 0.57692307692307692308 - вот оно самое.

✨Поэтому умножаем на 1.0, и вуаля✨

SELECT round(count1 * 1.0 / count2, 2)
FROM test

#sql_tips

👍Полезные советы

В продолжение поста про вредные советы есть еще несколько лайфхаков (уже нормальных!):

1️⃣ Бывает полезно сделать такое форматирование, когда мы играемся с полями, чтобы удобнее закомментить ненужные:

SELECT 
    e.emp_no
    , e.first_name
    --, e.last_name
    , s.salary
FROM employees e
  JOIN salaries s
    ON e.emp_no = s.emp_no

2️⃣ Добавлять элиасы таблиц к полям (sql или spark - неважно), чтобы под конец огромного запроса понимать, откуда тянутся данные. То же самое 👆на спарке:

final_df = (
  employees.alias('e')
  .join(salaries.alias('s'), F.col('e.emp_no') == F.col('s.emp_no'), 'inner')
  .select(
    'e.emp_no',
    'e.first_name',
    's.salary'
  )
)

3️⃣ А когда мы играемся с условиями, я частенько добавляю 1=1, чтобы тоже быстро закомментить и прогнать:

SELECT *
FROM employees
WHERE 1=1
     --AND date_joined <= '2024-01-01'
     AND is_deleted = 0
     AND username != 'system'

Если бы WHERE date_joined <= '2024-01-01' шло на первом месте, то пришлось бы убирать AND перед вторым условием, удалять лишние пробелы - в общем, неудобно, если часто так делать.

#sql_tips #spark

Задачка

Есть 3 таблички:

1️⃣ Cities (id, city, region)
2️⃣ Couriers (id, fio)
3️⃣ Orders (id, datetime, cost, city_id, courier_id)

Найти топ 10 курьеров для каждого города, которые доставили больше всего заказов за март.