предисловие
Только лысая голова может стать сильнее.
Текст был включен в мой репозиторий GitHub, добро пожаловать, звезда:GitHub.com/Zhongf UC очень…
Я недавно делал несколько отчетов в компании, до сих пор помню, что чуть не забыл про SQL, когда впервые начал делать отчеты (я был в отпуске в Гуанчжоу больше месяца, и во время стажировки не писал SQL). Первым требованием компании является отчетность.
Так что я без зазрения совести сказал взявшему меня старшему: "Я, кажется, забыл про SQL, и я, кажется, почти забыл о групповом запросе. Я должен его пересмотреть".
Эта статья предназначена для записи моегозабыватьНекоторые сравнения группового запроса, запроса на соединение и т. д.Практичный/ОбщийSQL
- Эта статья посвященаЛегко понять, не распространяется на какие-либо оптимизации (подходит для просмотра новичками)
1. Просмотр группового запроса
groupЗапрос — это групповой запрос, зачем вам групповой запрос? Потому что мы хотим считать по определенному измерению. Вот картинка:
Например, я хочу знать:每天Java3y这个公众号的点击量是多少。Согласно нашему мануалу, идея очень проста: узнать данные того же количества дней и имя публичного аккаунта Java3y, а затем сложить каждый клик, чтобы получить результат.
Используя SQL, мы могли бы написать:
select name,time,sum(pv) as pv
from xxx_table
where name = 'Java3y' group by name,time
1.1 Возможное неправильное понимание группового запроса
Помню, однажды друг группы задал вопрос в группе:
На самом деле его потребности очень просты: получить записи с наибольшим временем после группировки данных. Но вот что он сделал:
- поставить на первое место по времени
order by - правильно
order byПосле группировки записей
пример графика:
1.2 Возможные причины этого недоразумения
Некоторые инструменты поддерживают этот способ записи:
select * from xxx_table group by name
Этот способ записи не запрещен и может привести к таким результатам, как:
Java4y 20 7月15号
Java3y 30 7月15号
Этот способ написания на самом деле неразумен.Что вам нужно знать, так это: используйтеgroup byПосле группировки статистики за нашим выбором может следовать только поле группировки или функция агрегации.
Потому что мы делаем групповой запрос данных,Распределение данных, нам все равно.
Помните:Сначала сгруппируй, а потом считай(Сначала классифицируйте данные, а затем выполните статистику по тем же данным)
1.3 Наиболее часто используемый SQL для группового запроса
дедупликацияэто проблема, с которой мы часто сталкиваемся, например, по разным причинам (будь то бизнес или грязные данные), теперь у меня есть два повторяющихся данных (кроме ID, остальные поля одинаковые):
Я просто хочу оставить определенную запись в качестве результата запроса.Мы можем написать следующий SQL:
select * from user where id in(
select min(id) from user where name = 'Java3y' and pv = 20 and time='7-25' group by name,pv,time;
)
Вышеупомянутый SQL очень и очень практичен В дополнение к тому, что я сказал о дедупликации, мы можем снова «подумать»:
- Как упоминалось выше, используйте
group byПосле группировки статистики за нашим выбором может следовать только поле группировки или функция агрегации. - много раз мы
group byПосле этого вы также хотите включить в результаты запросаgroup byДругие поля (как правило, мы не можем покрыть все поля по группам), мы можем использовать результат запроса в качестве подзапроса, как указано выше, и поместить его после предложения where внешнего запроса, чтобы внешний запрос Можно выбрать другие поля.
(Друзья, которые пишут меньше SQL, возможно, этого не почувствуют, но я надеюсь, что все помнят вышеуказанный способ написания, и они обязательно столкнутся с ним в будущем.похожийкейс)
2. Просмотрите запрос на присоединение
Я не знаю, как вы поняли запрос на соединение, когда впервые узнали о нем.В любом случае, вначале я казался совершенно сбитым с толку. Я думаю, что запрос соединения можно просто понять так:Результаты запроса, которые я хочу, если одна таблица не может их обработать, я присоединюсь к другой таблице
Например, теперь у меня есть две таблицы:
Теперь я хочу знать на 25 июля: количество просмотров каждого официального аккаунта, официальное имя аккаунта, имя основного аккаунта, дату создания официального аккаунта.
- Очевидно, мы найдемНе могу справиться со столомАх, некоторые данные зависят от другой таблицы, чтобы отображать данные «полностью».
Это объединение на самом деле является операцией, объединяющей две таблицы:
После объединения двух таблиц мы обнаружим, что эта «большая таблица» содержит все поля двух таблиц, так что у меня есть все, что я хочу!
Стоит отметить, что: при соединении будет сгенерировано декартово произведение (я не буду говорить, что здесь такое декартово произведение, во всяком случае, что мы должны помнить, так этоПри объединении таблиц обязательно пропишите условия ассоциации, чтобы убрать декартово произведение)
Кроме того,left joinиright joinОн также часто используется нами, если мы просто напишемjoinключевое слово, которое будет рассматриваться какinner join. Позвольте мне кратко объяснить:
- Как было сказано выше, это должно быть написано при присоединенииУсловие ассоциации,если
inner join, в самой большой таблице будут храниться только те данные, которые соответствуют связанным условиям. - если
left join, даже если условия ассоциации не выполняются, данные в левой таблице обязательно будут существовать в большой таблице - если
right join, даже если условия ассоциации не выполняются, данные в правой таблице обязательно будут в большой таблице
См. рисунок ниже:
В настоящее время условием для ассоциации наших двух таблиц является «общедоступная учетная запись»: если даinner join, то, наконец, наша таблица имеет только две записи. еслиleft join, то, наконец, наша таблица содержит три фрагмента данных. еслиright join, то наша итоговая таблица содержит только два данных
3. Просмотрите случай, когда
Использование case when then else end в SQL фактически такое же, как и в нашем языке программирования.if-elseОчень похоже, часто используется при написании SQL.
Синтаксис, который я использую чаще всего, следующий:
CASE WHEN sex = '1' THEN '男'
WHEN sex = '2' THEN '女'
ELSE '其他' END
Несколько выражений могут следовать после, когда, например:
CASE WHEN sex = '1' and name ='Java3y' THEN '男'
WHEN sex = '2' and name ='Java4y' THEN '女'
ELSE '其他' END
Если хотитеcase whenВыражение имеет псевдоним, вendПросто добавьте его после ключевого слова
Дополнительные сведения об использовании см. по адресу:
В-четвертых, некоторые часто используемые функции
4.1 куст и предварительный разбор json
У меня здесь такая ситуация: сохранить данные json в MySQL. Когда я искал в Интернете и спрашивал своих коллег, почему я хочу хранить json в поле MySQL, их ответы были схожими:
- Храните данные json в MySQL, что удобно для расширения. Если эти поля не нужно индексировать, изменения происходят часто и вы не хотите менять структуру таблицы, вы можете сохранить соединение.
- ps: тип json поддерживается после версии MySQL 5.7
Использованная литература:
Обычно я делаю здесь отчеты по hive или presto, так что парсинг json тоже есть.
json-функция парсинга куста:
get_json_object(param1,'$.param2')
-- 如果是数组
get_json_object(xjson，'$.[0].param2')
Функция обработки Presto для json:
-- 数组 (去除第index个json)
json_array_get(xjson,index)
-- 单个jsoin对象
json_extract(xjson,'$.param2')
Использованная литература:
4.2 Функция времени
Также очень часто по этому индикатору запрашивают вчера/почти 7 дней/месяц:
昨天
SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 1
7天
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名)
近30天
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)
本月
SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )
上一月
SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( 时间字段名, '%Y%m' ) ) =1
Чтобы использовать формат времени в presto, вам нужно явно указать ключевые словаtimestamp,Например:
select supplier,count(id)
from xxx_table
where sendtime >= timestamp '2019-06-01'
Использованная литература:
4.3 Другие часто используемые функции
Здесь я кратко разберу функции, которые я использовал в последнее время:
length --计算字符串长度
concat --连接两个字符串
substring -- 截取字符串
count -- 统计数量
max -- 最大
min -- 最小
sum -- 合计
floor/ceil --...数学函数
Позвольте мне поделиться с вами недавно возникшим спросом Текущие данные следующие:
【Java3y简单】快乐学习
【Java3y简单】快乐学习渣渣
【Java3y通俗易懂】简单学
【Java3y通俗易懂】简单学芭芭拉
【Java3y平易近人】无聊学
【Java3y初学者】枯燥学
【Java3y初学者】枯燥学呱呱
【Java3y大数据】欣慰学
【Java3y学习】巴拉巴拉学
【Java3y学习】巴拉巴拉学哈哈
【Java3y好】雨女无瓜学
Теперь я подсчитываю частоту появления скобок [ ], например:Java3y通俗易懂Как часто он появляется. В то время мне никогда не приходило в голову хорошая идея, и я собирался искать «Быстрый запуск регулярных выражений SQL». Я попросил совета у своего коллеги, и мой коллега быстро написал это:
select substring_index(left(title , INSTR(title , '】') -1 ) , '【',-1)
FROM `xxx_table`
вау~, круто
Наконец
рад вывестигалантерейные товарыОбщедоступный номер технологии Java:Java3y. В публичном аккаунтеБолее 200 оригинальных статейТехнические статьи, обширные видеоресурсы, красивые карты мозга,Следуйте, чтобы получить его!
Я думаю, что моя статья хорошо написана, нажмитеотличный!