Этот толчок в основном включает в себя сложные подзапросы и вложенные функции в языке SQL.
Хотя эта серия статей о MySQL называется «Введение в основы MySQL», я не собираюсь делать краткий обзор использования одной функции или простой список (вы можете узнать об этом по-разному), потому что, с одной стороны, я предыдущий опыт изучения основ SQL (обязательные компьютерные курсы бакалавриата и компьютерные экзамены) теперь должен быть более углубленным.С другой стороны, SQL — это язык анализа данных, и простая одна или две функции редко могут решить проблемы.
В отличие от объектно-ориентированных языков, таких как язык R и Python, язык SQL предоставляет множество гибких и изменчивых методов и тысячи эффективных инструментов решения, и он не предоставляет инструменты передачи параметров, такие как функции конвейера, поэтому множественные для решения задачи в один раз, большую часть времени вам нужно использовать подзапросы и вложенность функций.
(Если вы новичок в языке SQL, лучше прочитать эту статью после прочтения одной или двух вводных книг или систематического понимания синтаксиса запросов SQL)
Материал практических данных для этой статьи взят из популярного курса Tianshan Intelligent Big Data Module — «Стать аналитиком данных за семь недель».
Курс г-на Циня сделал очень интересное резюме и обмен примерами для различных модулей, таких как бизнес-знания, навыки анализа, навыки программирования, необходимые аналитикам данных, и рекомендует друзей, которым нравится или интересно начать.
Поскольку это платный курс, неудобно приводить исходные данные здесь, прошу меня простить, но весь вывод кода в этой статье будет обеспечивать предварительный просмотр данных и описание полей в соответствующем месте.Эта статья является просто большой курсовой работой для меня, чтобы изучить модуль MySQL в нем. Используйте свои собственные идеи, чтобы реализовать его снова, и в то же время организовать код в соответствии с идеями учителя. Путем сравнения идей вы можете проверить и заполнить пробелы, а также улучшить умение практиковать код SQL.
В то же время я буду использовать общие инструменты анализа на языке R и Python для выполнения этой большой работы, чтобы читатели могли сравнить различия в процессах между тремя инструментами для достижения одинаковых требований и их соответствующих преимуществ и недостатков, а также углубить анализ. понимание процесса обработки данных.
Во-первых, давайте кратко представим эти два данных:
userinfo 客户信息表
userId 客户id
gender 性别
brithday 出生日期
orderinfo 订单信息表
orderId 订单序号（虚拟主键）
userId 客户id
isPaid 是否支付
price 商品价格
paidTime 支付时间
Приведенные выше две таблицы являются основным предметом этого анализа, где совпадающее поле — это userId.
Пять вопросов для этого анализа:
1. Подсчитайте количество заказов, сделанных в разные месяцы;
2. Статистика по выкупам и выкупам пользователей за март
3. Есть ли разница в частоте потребления пользователями мужского и женского пола
4. Каков интервал между первым и последним потреблением у пользователей, которые тратили много раз?
5. Статистика о том, есть ли различия в объеме потребления пользователей разных возрастных групп
6. Согласно правилу 28 статистики потребления, сколько потребляют первые 20% пользователей?
1. Подсчитайте количество заказов, сделанных в разные месяцы;
Первый вопрос относительно прост, вам нужно только преобразовать поле даты в метку месяца с помощью функции даты, а затем агрегировать количество людей, разместивших заказы за один месяц, в соответствии с меткой месяца!
Моя идея состоит в том, чтобы использовать функцию DATE_FORMAT для вывода ежемесячных меток записей о покупках, а затем использовать функцию агрегации group by function для агрегирования (подсчета) ежемесячных меток.При использовании count для подсчета мы должны учитывать ситуацию повторных покупок , дедуплицировать клиентов и получить реальное количество людей.
SELECT
DATE_FORMAT(paidTIme, '%Y-%m') AS MT,
count(DISTINCT userId) AS scale
FROM
orderinfo
WHERE
isPaid = '已支付'
GROUP BY
MT
Поскольку поля даты покупки относятся к одному и тому же году, все учителя используют функцию МЕСЯЦ напрямую, что более удобно!
SELECT
MONTH (paidTIme) AS MT,
count(DISTINCT userId) AS scale
FROM
orderinfo
WHERE
isPaid = '已支付'
GROUP BY
MT
2. Статистика по выкупам и выкупам пользователей за март
Вторые темы бизнес-треков должны понимать значение скорости выкупа и скорость повторной покупки (до того, как я был смущен, а позже пошел в расследование Baidu), повторная цена покупки равна количеству потребителей месяца, потребляет больше, чем Количество людей приходилось один раз, скорость закупки Back - это доля потребителей в месяц в месяц, снова потребление.
Рассчитайте коэффициент обратного выкупа (идея расчета коэффициента обратного выкупа аналогична таковой у учителя):
Сначала подсчитайте количество покупателей в марте и верните его как подзапрос.Внешний запрос использует функцию count+if для подсчета количества покупателей, совершивших покупку более одного раза, и разделит ее на общее количество людей, чтобы получить скорость выкупа.
SELECT
COUNT(if (thr.scale!=1,1,null)) as useful,
COUNT(*) as Fulln,
COUNT(if( thr.scale!=1,1,null))/count(*) as ratio
FROM
(
SELECT
userId,
count(userId) AS scale
FROM
db1.orderinfo
WHERE
MONTH (paidTime) = 3
AND isPaid = '已支付'
GROUP BY
userId
) AS thr
Рассчитайте коэффициент обратного выкупа (собственная идея):
Удалите дубликаты покупателей в марте, используйте count, чтобы подсчитать, сколько покупателей в марте появляется в покупателях в апреле (используя подзапрос в качестве условия фильтра, где), и сравните количество возвращенных записей результатов с общим числом покупателей в марте. , Разделите количество людей, чтобы получить скорость выкупа.
SELECT
count(DISTINCT userId) AS allfunn,
count(DISTINCT userId) / (
SELECT
count(DISTINCT userId)
FROM
orderinfo
WHERE
MONTH (paidTime) = 3
AND isPaid = '已支付'
) AS ratio
FROM
db1.orderinfo
WHERE
MONTH (paidTime) = 3
AND isPaid = '已支付'
AND userId IN (
SELECT DISTINCT
userId
FROM
db1.orderinfo
WHERE
MONTH (paidTime) = 4
AND isPaid = '已支付'
)
Что касается скорости выкупа, учитель использовал самостоятельную связь, которая едва ли может понять общую идею.Сравнивая, отличаются ли ежемесячные метки двух месяцев на один месяц, старые клиенты повторят покупку, если разница составляет один месяц. , так что это более применимо, когда есть много месяцев секса.
select t1.m,count(t1.m),count(t2.m) from (
select userId,DATE_FORMAT(paidTime,'%Y-%m-01') as m from db1.orderinfo
where isPaid = '已支付'
group by userId,date_format(paidTime,'%Y-%m-01')) t1
left join (
select userId,date_format(paidTime,'%Y-%m-01') as m from db1.orderinfo
where isPaid = '已支付'
group by userId,date_format(paidTime,'%Y-%m-01')) t2
on t1.userId = t2.userId
and t1.m = date_sub(t2.m,interval 1 month)
group by t1.m
3. Есть ли разница в частоте потребления пользователями мужского и женского пола
Эта проблема была осложнена мной. Я попросил частоту потребления мужчин и частоту потребления женщин соответственно! Идея состоит в том, чтобы сначала присоединиться к таблице пользователей и таблицу заказов, затем фильтруйте записи, гендерка которого является мужчиной и возвращает одну запись потребителей путем группировки. (Вычислительная аналогия для женщин по сравнению с мужчинами)
-- 男性消费频次
SELECT
SUM(mmg.mean) / count(*) AS mam_m
FROM
(
SELECT
orderinfo.userId,
ROUND(COUNT(orderinfo.userId), 1) AS mean
FROM
orderinfo
INNER JOIN userinfo ON orderinfo.userId = userinfo.userId
WHERE
userinfo.gender = '男'
GROUP BY
orderinfo.userId
) AS mmg
-- 女性消费频次：
SELECT
SUM(mmg.mean) / count(*) AS mam_m
FROM
(
SELECT
orderinfo.userId,
ROUND(COUNT(orderinfo.userId), 1) AS mean
FROM
orderinfo
INNER JOIN userinfo ON orderinfo.userId = userinfo.userId
WHERE
userinfo.gender = '女'
GROUP BY
orderinfo.userId
) AS mmg
男女消费频次（老师版）：
老师首先在连接两个表的基础上，剔除了无效记录，通过count做了单个购买者的购买数量统计，
然后使用了基于性别的分组均值聚合，输出男女性平均消费频次。这个思路太棒了，我特么的就是想不到~_~
SELECT
gender,
AVG(ct)
FROM
(
SELECT
o.userId,
gender,
count(1) AS ct
FROM
orderinfo o
INNER JOIN (
SELECT
*
FROM
db1.userinfo
WHERE
gender != ''
) t ON o.userId = t.userId
GROUP BY
userId,
gender
) t2
GROUP BY
gender
4. Каков интервал между первым и последним потреблением у пользователей, которые тратили много раз?
Эта тема заняла у меня больше всего времени.На самом деле, логически уверен, что все знают, что необходимо отфильтровать те записи с более чем одним потреблением, а затем пропустить самое дальнее время потребления и самое последнее потребление среди все записи о потреблении одного покупателя Время делать разницу во времени. Это легче сказать, чем сделать. Поэтому я написал такой код!
SELECT
* FROM
(
SELECT
userId,
DATEDIFF(
myresult.uptime,
myresult.odtime
) AS difftime
FROM
(
SELECT
lowf.userId,
lowf.odtime,
UPf.uptime
FROM
(
SELECT
userId,
min(ldd.ltime) AS odtime
FROM
(
SELECT
userId,
paidTime AS ltime
FROM
orderinfo
WHERE
isPaid = '已支付'
ORDER BY
userId,
Ltime
) AS ldd
GROUP BY
userId
) AS lowf
INNER JOIN (
SELECT
userId,
max(pdd.ptime) AS uptime
FROM
(
SELECT
userId,
paidTime AS ptime
FROM
orderinfo
WHERE
isPaid = '已支付'
ORDER BY
userId,
ptime DESC
) AS pdd
GROUP BY
userId
) AS UPf
ON lowf.userId = UPf.userId
) AS myresult
) AS myresult1
WHERE
difftime != 0
Моя общая идея заключается в том, что самая внутренняя логика состоит в том, чтобы сначала отфильтровать самую дальнюю запись потребления потребителя и самую последнюю запись потребления и внутренне соединить два выхода. На основе выходной таблицы сделайте разницу во времени.Если время равно 0, это означает, что есть только одно потребление, и вы можете напрямую использовать difftime != 0 для фильтрации.
Вот идея, подсказанная учителем. Прочитав ее, я воскликнул, что мне стыдно. Вы видите, что внутренняя связь выше лишняя. Ее можно решить, используя две функции max и min для сопоставления полей, но то, что я написал, слишком сложно! Может даже убежать.
SELECT
userId,
min(paidTime) as mintime,
max(paidTime) as maxtime,
datediff(
max(paidTime),
min(paidTime)
) as difftime
FROM
db1.orderinfo
WHERE
isPaid = '已支付'
GROUP BY
userId
HAVING
count(1) > 1
5. Статистика о том, есть ли различия в объеме потребления пользователей разных возрастных групп
На первый взгляд, я не совсем понял этот вопрос, сначала думал, как определить эту возрастную группу (точное определение не дано), а потом задумался о пост-70-х, пост-80-х и пост-80-х. 90-е годы, когда я говорю о возрастных агентах по закупкам.Что, я подумал, что это можно использовать в качестве основы для возрастных групп.
Моя личная общая идея заключается в том, что самый внутренний слой сначала объединяет две таблицы (отфильтровывая пропущенные значения и неоплаченные записи при объединении), а затем средний слой классифицирует и кодирует дату рождения (после 70 с 1970 по 1979 год, так что аналогия).
Наконец, самый внешний слой получает среднее значение платных цен для разных возрастных групп путем группировки и агрегирования возрастных групп.
SELECT
trend,
round(avg(price), 2) AS means
FROM
(
SELECT
userId,
btdate,
price,
CASE
WHEN btdate BETWEEN '1970-01-01' AND '1979-12-31' THEN '70后'
WHEN btdate BETWEEN '1980-01-01' AND '1989-12-31' THEN '80后'
WHEN btdate BETWEEN '1990-01-01' AND '1999-12-31' THEN '90后'
WHEN btdate BETWEEN '2001-01-01' AND '2009-12-31' THEN '00后'
ELSE '10后'
END
AS 'trend'
FROM
(
SELECT
o.userId,
price,
date(brith) AS btdate
FROM
orderinfo o
LEFT JOIN (
SELECT * FROM
db1.userinfo
WHERE
gender != ''
) t ON o.userId = t.userId
WHERE
isPaid = '已支付'
AND
date(brith) != '0000-00-00'
) AS mt
ORDER BY
userId
) AS outtable
GROUP BY
trend
ORDER BY
means
Относительно разницы в количестве потребления в возрастной группе учитель дал идею:
SELECT
age,
AVG(ct)FROM
(
SELECT
o.userId,
age,
count(o.userId) AS ct
FROM
db1.orderinfo o
INNER JOIN (
SELECT
userId,
CEIL((YEAR(now()) - YEAR(brith)) / 10) AS age
FROM
db1.userinfo
WHERE
brith > '1901-00-00'
) AS t
ON o.userId = t.userId
GROUP BY
o.userId,
age
) t2
GROUP BY
age
Вышеупомянутый учитель рассчитал среднюю частоту потребления покупателей в каждой возрастной группе. Здесь учитель использует функцию даты, чтобы заменить отдельную работу по кодированию, благодаря чему весь код выглядит очень кратким и простым для понимания. (Многому еще можно научиться самостоятельно!)
6. Согласно правилу 28 статистики потребления, сколько потребляют первые 20% пользователей?
На самом деле логика задачи правила 28 очень проста, то есть в соответствии с общей суммой потребления одного потребителя вычислить долю верхних 20% суммы потребления покупателя к общей сумме потребления. Хотя логика очень проста, написать вторичную логику в MySQL непросто, потому что MySQL не поддерживает функцию n первых, и вы можете фильтровать только первые n записей, добавляя параметр limit.
Поэтому я сам написал два фрагмента кода, чтобы решить эту проблему:
Во-первых, отсортируйте по общей сумме покупок одного потребителя и подсчитайте количество потребителей, чьи общие расходы входят в топ-20. (всего 17130)
SELECT
ceil(count(*) / 5)
FROM
(
SELECT
userId,
sum(price) AS allsp
FROM
orderinfo
WHERE
date(paidTime) != '0000-00-00'
AND isPaid = '已支付'
GROUP BY
userId
ORDER BY
allsp DESC
) AS spend
-- 17130
Затем снова запустите запрос, используйте параметр limit для ограничения вывода первых 17130 записей и рассчитайте долю их общего количества ко всем объемам потребления.
SELECT
(
SELECT
sum(allsp) AS top20
FROM
(
SELECT
userId,
sum(price) AS allsp
FROM
orderinfo
WHERE
date(paidTime) != '0000-00-00'
AND isPaid = '已支付'
GROUP BY
userId
ORDER BY
allsp DESC
LIMIT 17130
) AS spend
) / (
SELECT
sum(allsp) AS entry
FROM
(
SELECT
userId,
sum(price) AS allsp
FROM
orderinfo
WHERE
date(paidTime) != '0000-00-00'
AND isPaid = '已支付'
GROUP BY
userId
ORDER BY
allsp DESC
) AS spend
) as top20ratio
Результат расчета около 85,46%.
Из-за нехватки места версии этих пяти вопросов на языке R и версии Python с нетерпением ждут следующего выпуска!
Несколько личных мыслей:
1. Поскольку все предыдущие навыки очистки и обработки данных были отработаны на языке R, внезапное использование SQL для этого, даже если это очень простая логика требований, кажется, что писать неровно, короче говоря, это не гибко. использовать, простые Проблемы часто бывают сложными.
2. Оператор запроса в SQL имеет фиксированный шаблон, и весь вывод должен строго зависеть от выбора ...... из ...... Разделить на несколько промежуточных шагов, необходимо использовать подзапросы.
3. SQL не имеет такого же оператора конвейера, как язык R или вызовы методов в Python. Шаги многозадачности могут полагаться только на вложенные в предложение подзапросы. Требования немного сложнее. Если использование базовых функций недостаточно гибко , может быть будет сложно писать.
Синтаксис SQL-запроса необходимо решать, используя как можно больше собственных функций на основе глубокого понимания взаимосвязей таблиц, что одновременно эффективно и экономит код.В приведенном выше коде, написанном мной, есть много избыточных мест, и в будущем потребуется дополнительная работа Практика, укрепление практики в различных сценариях и гибкое использование ее для написания лаконичного, эффективного и многократно используемого кода задачи.
Следующая ссылка представляет собой серию семинедельных курсов по анализу данных, которые преподает г-н Цинь Лу в колледже Тянь-Шань!
Семинедельный курс, семь основных навыков для аналитиков данных, шаг за шагом, пробивайте один за другим, рекомендуется для изучения тем, кто интересуется анализом данных и бизнес-анализом!