13 функций Windows 10, про которые вы могли не знать. Есть режим Бога
В мире настольных ПК и ноутбуков Windows 10 – с огромным отрывом преобладающая операционная система, и пользоваться ей умеет практически каждый. Но как и в любой ОС, здесь есть ряд скрытых, но очень полезных функций, о наличии которых вы могли даже не догадываться.
Например, вы знали, что можно быстро перейти к документу или веб-странице, которую вы открывали несколько дней назад, более эффективно организовать открытые приложения, легко делать скриншоты и видео из игр, вести заметки, которые затем можно просматривать на других устройствах?
В этой статье я расскажу о том, что это за функции и как их найти в Windows 10. Они не так широко известны, но точно помогут вам работать эффективнее.
1. Игровой режим
Начну, пожалуй, с наиболее ценной фишки лично для меня, как для геймера.
Активируется этот режим так: Параметры (клавиши Win+I) — Игры – пункт «Режим игры».
Игровой режим позволяет повысить быстродействие, перераспределяя ресурсы системы. А по комбинации клавиш Win+G можно запустить Игровую панель, которая позволяет записывать игровые ролики, делать скриншоты, общаться с друзьями из сервиса Xbox Live и даже стримить.
2. Настройки конфиденциальности
Microsoft наконец-то смогла нормально реализовать настройку параметров конфиденциальности, но не особо рассказывает, как правильно ими пользоваться. По умолчанию Windows 10 собирает тонны информации о вас и бессовестно хранит всё на своих серверах.
Чтобы по минимуму делиться своими данными с «мелкомягкими», нужно пройти по пути Параметры (клавиши Win+I) – Конфиденциальность.
Далее желательно запретить ВСЕ пункты, особенно в разделах « Общие», «Речь, рукописный ввод и ввод текста» и «Расположение».
3. Режим Бога
Одна из самых скрытых и самых мощных функций под пафосным названием «Режим бога» (GodMode) позволяет вам получить доступ к специальному меню.
Здесь в одном месте собрано множество тонких настроек системы, включая некий аналог TimeMachine. Открыть это меню не так просто – нужно создать в корне системного диска новую папку и переименовать ее в GodMode.{ED7BA470-8E54-465E-825C-99712043E01C}.
У нее поменяется значок, а при открытии запустится симпатичная продвинутая панель управления.
4. Виртуальные рабочие столы
Работа с виртуальными рабочими столами – одно из лучших нововведений Windows 10 по сравнению с предыдущими версиями. Уже сам факт их поддержки – значительный шаг вперед, потому что у пользователей Mac OS X и Linux такая возможность есть уже давно.
Теперь вы можете, например, разместить какие-то окна и задачи на одном рабочем столе, электронную почту и Twitter – на другом, а серфинг в интернете – на третьем.
Чтобы воспользоваться этой функцией, кликните по значку Представление задач на панели задач (сразу справа от поля поиска), или нажмите Win+Tab.
На экране вы увидите надпись Создать рабочий стол, под которой будет пустая область с плюсиком. Можно просто по ней кликнуть, а можно сразу перетащить на нее какое-нибудь приложение.
5. Печать в PDF
При помощи виртуального принтера Microsoft Print to PDF вы можете «распечатать» (фактически просто сохранить) документ в PDF без необходимости использовать сторонние приложения.
Просто выберите соответствующий «принтер» в диалоговом окне печати любого приложения. Это полезно для сохранения и распространения документов без возможности редактирования.
6. Скроллинг неактивных окон
Когда-нибудь сталкивались с тем, что вы наводите курсор мыши на окно и пытаетесь его прокрутить, но не можете, потому что оно неактивно?
Это можно исправить. Включите функцию Прокручивать неактивные окна при наведении на них в окне Параметры – Устройства – Мышь и сенсорная панель.
7. «Встряска» для быстрого сворачивания всех окон
Если у вас открыто много окон, можно навести курсор на строку заголовка любого из них, зажать левую кнопку мыши и «встряхнуть» его.
Тут же все остальные окна будут свернуты.
Возможно, потребуется немного потренироваться – у меня, например, с первого раза не получилось. Если повторить встряхивание, окна снова развернутся.
8. Секретное меню «Пуск»
Интерфейс Windows 10 визуально отличается от предыдущих версий операционной системы, но в целом все работает одинаково.
Правый клик по значку Windows в нижнем левом углу (или комбинация клавиш Win+X) открывает контекстное меню. Оно позволяет быстро осуществлять доступ к многим функциям управления (
Это секретное меню избавит от необходимости пробираться к этим параметрам через лабиринты панелей и меню.
9. Быстрый обмен файлами с устройствами поблизости
Открытым документом или фотографией можно быстро поделиться с устройствами неподалеку, наподобие AirDrop у Apple.
Для использования этой возможности включите параметр Обмен с устройствами поблизости в разделе Параметры – Система – Общие возможности.
После этого вы должны видеть доступные для передачи устройства поблизости при любом нажатии кнопки Поделиться везде, где она есть.
10. Темная тема и другие цвета
В Windows 10 у вас значительно больше возможностей по управлению цветовыми темами.
Откройте Параметры – Персонализация – Цвета, и сможете выбрать светлую или темную тему. Она изменяет цвета меню Пуск, панели задач, проводника, меню параметров и любых других программ, использующих системные настройки.
Можно даже выбрать для Windows одну тему, а для приложений – другую. Причем помимо темной и светлой тем можно раскрасить операционку на свой вкус: разнообразие цветовых решений просто поражает.
11. Облачный буфер обмена
До обновления в октябре 2018 года буфер обмена Windows 10 ничем не отличался от того, что было раньше.
Но сегодня вы можете сохранять туда по несколько объектов и вставлять их на разных устройствах. Для включения этого режима откройте Параметры – Система – Буфер обмена и включите параметры Журнал буфера обмена и Синхронизация между устройствами.
12. Временная шкала
Нажав на кнопку Представление задач, вы увидите временную шкалу с образами различных действий, которые вы недавно выполняли на своем компьютере, таких как работа с документами и посещение веб-сайтов. Если она отсутствует, включите ее в параметрах системы, как показано ниже.
Эта история упорядочена по времени. Она начинается с текущего момента и отматывается назад. Здесь вы можете найти документ, над которым вы работали пару дней назад, или открытую на прошлой неделе веб-страницу, и снова туда зайти.
13. Записки на рабочем столе
О том, что в Windows 10 есть встроенное приложение для ведения записей под названием OneNote, знает практически каждый. Но иногда весь его мощный функционал не требуется – нужно просто быстро что-то записать и сохранить на будущее, как на бумажных стикерах.
Здесь и поможет это приложение. Оно позволяет создавать короткие заметки, которые, в отличие от бумажных наклеек, можно синхронизировать между всеми устройствами с Windows 10.
Для запуска просто введите в строке поиска Записки и кликните по предложенному варианту. Чтобы синхронизировать записки между несколькими устройствами с Windows 10 под одной учётной записью,, при первом запуске потребуется привязка к аккаунту Microsoft.
Да, со времен «Висты» Windows стала практически удобной для ежедневного использования, не в последнюю очередь благодаря этим мелким нюансам. Признавайтесь, знали о них до прочтения?
🤓 Хочешь больше? Подпишись на наш Telegram. … и не забывай читать наш Facebook и Twitter 🍒 В закладки iPhones.ru Да это же почти как у Apple!Рустам Багримов
@Monty_L- До ←
Победитель всемирного конкурса параноиков и его простой графический ключ на смартфоне
- После →
WSJ: массовое производство iPhone 12 отложили на месяц
10 самых полезных функций Windows 10
Лето означает, что у многих людей наступила пора отпусков и что за мониторами компьютеров они проводят времени меньше, чем обычно. Однако Microsoft решила в этом году представить свою новую операционную систему как раз в это время, а именно 29 июля. В этот же день крупные производители компьютеров начнут поставки устройств с Windows 10.
В Windows 10 компания обещает исправить недостатки Windows 8 и значительно видоизменить свою программную платформу, чтобы она была более приспособленной к требованиям современного компьютерного рынка. Программа тестирования Windows Insider показала, что Microsoft как никогда готова слушать мнение пользователей, а заодно подогрела интерес к системе. Ресурс Winline составил список функций Windows 10, которые сделают работу с «виндой» проще и удобнее.
1. Меню «Пуск»
После отсутствия в Windows 8, меню «Пуск» возвращается в новом обличии. Именно из-за его отсутствия многие не стали переходить на Windows 8; теперь меню предлагает сочетание традиционного подхода и плиток начального экрана, которые будут знакомы и привычны тем, кто всё же не побоялся познакомиться с Windows 8.
2. Центр уведомлений
В чём операционная система от Microsoft отставала от продуктов конкурентов, так это в доставке уведомлений. Как в Windows 7, так и в Windows 8 не было единого места, где собирались бы все уведомления системы и приложений. Центр уведомлений в Windows 10 устраняет этот недостаток. Он доступен как в версии системы для ПК, так и для смартфонов, устраняя необходимость открывать отдельные приложения, чтобы узнать суть уведомления.
3. Голосовой помощник Cortana
Ещё одна позаимствованная из мобильной платформы функция – голосовой ассистент Cortana. Эта программа нашла применение во многих сегментах Windows 10, в частности, в поиске внутри системы и в глобальной сети. Функция настолько важна, что компания Toshiba в своих ноутбуках на Windows 10 даже выделила для её запуска отдельную клавишу на клавиатуре.
К сожалению, Cortana пока недоступна российским владельцам Windows 10, и когда она заработает, пока не ясно. Со временем, когда голосовой помощник будет работать во всех регионах, возможности машинного обучения сделают Cortana ещё лучше.
4. Continuum
Интересная функция для обладателей смартфонов на Windows 10. Она превращает смартфоны в настоящие компьютеры при подключении к ним клавиатуры, мыши и монитора. Интерфейс мобильной операционной системы меняется на интерфейс версии для ПК. Ряд производителей уже готовят специальные док-станции для смартфонов, которые позволят подключать к ним периферийные устройства.
Помогают в этом единая Windows на всех устройствах Microsoft и ставка на облачные технологии. Если всё получится как задумано, смартфон может стать единственным компьютерным устройством пользователей, как мобильным, так и стационарным.
5. Переключение приложений
Не все новые функции Windows 10 должны стать приковывающими взгляд сенсациями, некоторые являются лишь итогом эволюционного развития системы. Например, сочетание клавиш Alt + Tab. Раньше с их помощью можно было переключаться между программами, то же самое происходит и теперь, только с улучшенным внешним видом и расположением окон.
6. Режим Snap для управления окнами
Попытка Microsoft проникнуть на территорию архитектуры ARM в виде Windows RT успехом не увенчалась, однако в ней было кое-что хорошее. Функция Snap позволяет интеллектуально располагать окна бок о бок друг с другом. Особенно полезна она на больших экранах, где есть много свободного места. Как вы уже поняли, эта функция есть в системе Windows 10 и стала ещё лучше, чем прежде.
7. Браузер Edge
В 90-е годы был представлен браузер Internet Explorer, и хотя с каждой версией он становился лучше, по функциям отставал от многих конкурентов. Новый браузер Microsoft получил название Edge и избавляясь от устаревшего кода IE, лучше соответствует спецификациям современного веба. Это делает его быстрее в работе, а функции вроде поддержки ввода стилусом ещё больше повышают интерес пользователей.
8. Универсальные приложения
Windows 10 положит конец фрагментации программных продуктов Microsoft. На ней будут работать компьютеры, планшеты, смартфоны, приставки и ряд других устройств. Универсальные приложения также должны работать на разных типах устройств, с изменениями в пользовательском интерфейсе. Это должно заполнить магазин приложений Windows Store и показать видение Microsoft будущего операционных систем.
9. Windows Hello
В последние годы, особенно после появления iPhone 5s, сканеры отпечатков пальцев встречаются в мобильных устройствах всё чаще. Windows Hello является функцией биометрической аутентификации, которая позволит разблокировать компьютер при помощи распознавания лица или сканирования радужной оболочки глаза пользователей. Тем самым пользователи могут быть избавлены от необходимости запоминать и вводить пароли. Насколько надёжной и востребованной окажется эта функция, покажет будущее.
10. DirectX 12
Компьютеры становятся всё мощнее, а ядер в центральных и графических процессорах всё больше. Однако пока возможность обмениваться информацией между этими ядрами была ограниченной. Только одно ядро могло общаться с другим одновременно. Графический интерфейс DirectX 12 должен улучшить положение дел, и множество ядер смогут общаться разом, повышая эффективность использования энергии. Разработчики за счёт этого смогут улучшить световые эффекты и другие графические возможности.
Windows 10 станет последней операционной системой Microsoft. В дальнейшем платформа будет развиваться по модели «программное обеспечение как сервис». Операционная система больше не будет получать крупных обновлений раз в несколько лет, она будет постоянно изменяться, приобретая новые и теряя ненужные функции.
Полезные функции Windows 10, которые помогут каждому
Windows 10, будучи последней актуальной и обновляемой версией операционной системы от Microsoft, содержит в себе массу полезных функций. Это может быть “Центр уведомлений”, “Голосовой помощник Cortana”, пришедший на замену Internet Explorer браузер “Edge”, “DirectX 12” и многие другие дополнения.
Тем не менее существует и множество других полезных функций, о которых мы даже не слышали! Все потому что они хоть и есть в операционной системе, однако редко попадаются на глаза, теряясь под длинным списком основных параметров.
Для этой статьи мы отобрали список наиболее полезных, на наш взгляд, функций Windows 10, которые могут быть полезны любому пользователю!
Содержание статьи:
Анализатор дискового пространства
Любой компьютер время от времени нуждается в очистке устаревших данных, временных файлов, дубликатов и прочих документов, засоряющих диск. Раньше для выполнения этой задачи мы были вынуждены использовать сторонние приложения, поскольку не имели в наличии достойных альтернатив в родной системе.
Время шло и Microsoft поняли, что добавление дискового анализа Windows окажется весьма полезной функцией, способной избавить пользователей от необходимости поиска ненадежных сторонних программ.
Как запустить анализатор?
1. Откройте диалоговое окно меню Пуск при помощи горячих клавиш Win + Q и введите Память устройства. Запустите утилиту.
2. Выберите диск, который нуждается в анализе.
Сканирование не отнимет много времени.
3. По завершению анализа, Windows предоставит вам подробный отчет о всех найденных файлах.
Теперь, на примере временных файлов, давайте рассмотрим процесс удаления ненужных документов.
1. Откройте Временные файлы.
2. Отметьте галочками тип документов, который вы хотите очистить, и нажмите клавишу Удалить файлы.
Удаление приложений из меню Пуск
Согласитесь, в современном мире нет ничего важнее экономии личного времени. Поэтому мы подготовили для вас небольшой совет, который позволит избежать перехода по различным окнам меню в поисках инструмента по удалению ненужного приложения.
- Откройте меню Пуск.
- Щелкните правой кнопкой мыши по интересующему вас приложению и нажмите клавишу Удалить.
Готово! Осталось дождаться завершения процесса удаления.
Дополнительные параметры командной строки
Всем нам очень хотелось увидеть эту функцию — возможность использования комбинации клавиш Ctrl + V в командной строке. Наконец-то наши желания были услышаны и с выходом Windows 10 компания Microsoft анонсирования возможность вставки текста, который не так давно мы могли набирать по пять минут.
Если вы щелкните правой кнопкой мыши по заголовку утилиты и откроете ее свойства, то обнаружите дополнительные параметры, которые всегда можно настроить под себя. Тем не менее, рекомендуем вносить изменения только если вы четко уверены в том, что делаете.
Полезные сочетания клавиш
Знание нескольких сочетаний клавиш для упрощения нашей цифровой жизни — это хорошо, а знание еще большего количества таких клавиш может существенно помочь в экономии времени.
Большинство классических комбинаций по прежнему работают, однако Microsoft решили ввести новые. Давайте их рассмотрим:
- Win + Tab. Позволяет просмотреть все виртуальные рабочие столы одновременно.
- Win + Q или Win + S. Позволяет запустить голосового помощника Cortana.
- Win + A. Позволяет открыть центр уведомлений.
- Win + I. Позволяет открыть настройки Windows 10.
- Win + Ctrl + D. Позволяет создать новый виртуальный рабочий стол.
- Win + Ctrl + F4. Позволяет закрыть текущий виртуальный рабочий стол.
- Win + Ctrl + (клавиши Вправо/Влево). Позволяет переключаться между рабочими столами.
- Win + G. Позволяет открыть игровую панель.
Уведомления Windows 10
Удобная функция, введенная с релизом Windows 10. Открыв уведомления, вы получаете не только информацию о последних важных событиях в жизни вашего компьютера, но и получаете быстрый доступ к основным параметрам системы.
Восстановление потерянного дискового пространства
Очистка диска является одной из старых утилит Windows. Тем не менее за многие годы она не потеряла в своей эффективности и по прежнему часто используется многими пользователями. Найти данное приложение вы можете просто открыв панель управления и введя название утилиты.
Щелкните по любому из представленных параметров в списке и изучите файлы, которые может удалить данная программа. Поставьте галочки на нужные пункты и завершите очистку.
Удаленный помощник
Если член вашей семьи или близкий друг столкнулся с определенными неполадками внутри компьютера, должно быть, вам сразу приходит на ум помочь ему, используя TeamViewer или Skype, чтобы своими глазами увидеть происходящее.
Однако компания Microsoft создала удобную утилиту, позволяющую организовать удаленную работу с другим устройством без необходимости устанавливать стороннее программное обеспечение.
Как запустить Удаленный помощник?
1. Откройте Панель управления.
2. Откройте параметр Система и безопасность.
3. В разделе Система кликните по Запуск удаленного помощника.
4. Выберите первый представленный параметр и следуйте дальнейшим инструкциям Windows.
Таким образом вы с легкостью установите удаленную связь с любым компьютером.
Проверка диска на наличие ошибок
Команда, которую мы рассмотрим ниже, способна устранить большинство ошибок, которые могут возникнуть на вашем диске. Она проста в использовании и при этом невероятно эффективна.
1. Откройте меню Пуск и введите команду cmd.
2. Запустите утилиту от имени администратора.
3. В открывшемся окне введите sfc /scannow.
Будьте готовы к тому, что проверка отнимет некоторое время.
4. Дождитесь завершения процесса сканирования и исправления ошибок.
5. Перезапустите компьютер.
Вы также можете попробовать команду chkdsk /f, чтобы устранить возможные дисковые неполадки.
Планировщик заданий
Планировщик заданий — довольно старая утилита, используемая Microsoft в их операционной системе еще с давней версии Windows 95. Как следует из названия, она позволяет руководить графиком системных процессов внутри Windows.
Это может быть планирование перезапуска компьютера или же определение времени на которое антивирус перейдет в состояние бездействия. Все эти действия выполняет “Планировщик заданий”.
Как открыть приложение? Очень просто!
- Запустите Панель управления.
- Введите в диалоговом окне Планировщик заданий и запустите программу.
Защитник Windows
Уже давно прошло время, когда системный антивирус можно было назвать совершенно бесполезным. Если вы производите стабильные обновления операционной системы, Windows Defender предоставит вам максимально возможную защиту, поскольку Microsoft всегда были заинтересованы в качестве продукта и безопасности своих пользователей.
Если вы не хотите покупать лицензию на сторонние антивирусные приложения, Защитник Windows всегда готов надежно защитить вас от возможных угроз.
Проверка отпечатков пальцев
Это средство защиты, пожалуй, представляет собой наивысшую степень безопасности для любого пользователя. Как вы наверняка знаете, даже некоторые телефоны начинают поддерживать подобную технологию и не за горами время, когда эта система может выйти на рынок и стать общеиспользуемой.
Тем не менее, сейчас далеко не у каждого пользователя ПК есть в наличии устройства, способные проверять отпечатки пальцев. Хотя, важно заметить, некоторые ноутбуки уже вполне успешно справляются с данной задачей.
С таким быстрым техническим ростом вполне возможно, что в скором времени мы окончательно откажемся от ввода паролей вручную.
Надеемся, статья оказалась для вас полезной и помогла найти ответы на поставленные вопросы!
Похожие статьи про восстановление данных:
Дата:
Теги: Windows, Диск, Носитель информации, Процесс восстановления
Структуры и функции Windows API
Структуры и функции Windows APIВ данном разделе приведены «вольные» переводы некоторых часто используемых структур и функций из Windows API.
Функции Windows API
| Функция | Описание |
|---|---|
| GetFileTime | возвращает дату и время создания, модификации и последнего доступа к файлу |
| PeekConsoleInput | считывает данные из указанного консольного входного буфера без удаления |
| ReadConsoleInput | считывает данные из входного буфера консоли и удаляет их оттуда |
| SetFileTime | устанавливает дату и время создания, модификации и последнего доступа к файлу |
| WriteConsoleInput | записывает данные непосредственно в консольный входной буфер |
Структуры Windows API
| Структура | Описание |
|---|---|
| CHAR_INFO | определяет код символа и цветовые атрибуты экранной символьной ячейки |
| CONSOLE_CURSOR_INFO | содержит информацию о консольном курсоре |
| COORD | определяет координаты ячейки символа в консольном буфере экрана |
| FILETIME | число сто-наносекундных интервалов, прошедших с 1 января 1601 года |
| FOCUS_EVENT_RECORD | содержит сообщения фокуса; является членом структуры INPUT_RECORD |
| INPUT_RECORD | информация о входных сообщениях в консольном входном буфере |
| KEY_EVENT_RECORD | клавиатурные события консоли; является членом структуры INPUT_RECORD |
| MENU_EVENT_RECORD | сообщения меню; является членом структуры INPUT_RECORD |
| MOUSE_EVENT_RECORD | информация о сообщениях мыши в консольном окне; является членом структуры INPUT_RECORD |
| RECT | определяет координаты левого верхнего и правого нижнего углов прямоугольника |
| SMALL_RECT | определяет координаты левого верхнего и правого нижнего углов прямоугольника |
| SYSTEMTIME | хранит дату и время, используя отдельные поля для месяца, дня, года, дня недели, часов, минут, секунд и миллисекунд |
| WIN32_FIND_DATA | описывает файл, найденный функциями FindFirstFile, FindFirstFileEx или FindNextFile |
| WINDOW_BUFFER_SIZE_RECORD | информация об изменениях размеров экранного буфера консоли; является членом структуры INPUT_RECORD |
Константы Windows API
| Константа | Описание |
|---|---|
| Virtualkeycodes | виртуальные коды клавиш |
Смотрите также:
7 новых функций Windows 11, которых мы не ожидали
Мы знали, что утечка сборки Windows 11, которую все опробовали на прошлой неделе, была ранней и незаконченной. Мы не знали, что еще Microsoft приготовила для Windows 11 — приложения для Android!
Мы с нетерпением ждем возможности изучить некоторые из этих функций, когда Microsoft начнет выпуск первых сборок Windows Insider для Windows 11 на следующей неделе. До тех пор все, что у нас есть, — это новые интригующие тизеры новых функций Windows 11, о которых мы не знали.
Приложения Android на вашем ПК с Windows 11
Директор по продуктам Microsoft Панос Панай сказал, что его «накачали», чтобы перенести Android-приложения на ПК, но почему бы и нет? Приложения для Android — одно из преимуществ платформы Google Chrome OS, на которой работают конкурирующие Chromebook. Их добавление к Windows 11 означает, что количество приложений, доступных для ПК с Windows, потенциально вырастет намного быстрее и больше. Microsoft нечасто выделяет количество приложений для Windows, но в 2018 году Microsoft сообщила, что существует 35 миллионов приложений с поддержкой Windows, включая устаревшее программное обеспечение. AppBrain оценивает количество приложений для Android примерно в 3 миллиона.
Неясно, будет ли Microsoft переносить все приложения Android на ПК, поскольку они появятся в Microsoft Store, а не в версии Google Play Store для ПК. Microsoft продемонстрировала Khan Academy, Kindle Reader, TikTok и другие на скриншоте в качестве примера.
Чтобы перенести приложения Android на ПК с Windows, Microsoft использует технологию Intel Bridge, которую Intel описывает на высоком уровне как «пост-компилятор времени выполнения, который позволяет приложениям запускаться изначально на устройствах на базе x86, в том числе запускать эти приложения в Windows».
Похоже, что Windows 11 будет выполнять эту работу на вашем компьютере, а не в Microsoft Store. Мы до сих пор не знаем, является ли технология Intel Bridge каким-то образом эксклюзивной для процессоров Intel.
Teams в чате на панели задач
Нравится вам это или нет, но Microsoft настаивает на том, чтобы вы могли легко обращаться к контактам с рабочего стола Windows. Все началось с уже не существующего приложения People, а затем перешло в Skype Meet Now на панели задач. Теперь мы переходим к Teams и помещаем Teams Chat на панель задач Windows 11.
«В Windows 11 мы рады представить чат из Microsoft Teams, интегрированный в панель задач», — говорится в сообщении Microsoft в блоге, представляющем Windows 11. «Теперь вы можете мгновенно подключаться через текст, чат, голос или видео со всеми своими контактами в любом месте, независимо от платформы или устройства, на котором они находятся, в Windows, Android или iOS. Если человек, с которым вы подключаетесь на другом конце, не загрузил приложение Teams, вы все равно можете связаться с ним с помощью SMS ».
Вы также сможете мгновенно отключать и включать людей в разговор прямо с панели задач и даже начать презентацию во время звонка в Teams.
Виджеты
Мы мельком познакомились с виджетами в слитой сборке Windows 11, но, судя по официальной презентации Microsoft, это явно не все. Прямо сейчас Widgets — это огромная карточка, которая выдвигается с левой стороны вашего рабочего стола. Похоже, что это комбинация новостей и Windows 10, вашего телефона и т. Д., С новостями, уведомлениями и персонализированной информацией различных типов, передаваемых на ваш компьютер.
Будут ли специализированные приложения-виджеты? Похоже, да. Есть даже интересная функция, которую Microsoft встроила в трансляцию, описывающую новую функцию виджетов: возможность давать чаевые местным создателям контента.
«Наше стремление состоит в том, чтобы создать динамичный конвейер как для мировых брендов, так и для местных авторов, который принесет пользу как потребителям, так и создателям», — заявила Microsoft.
Обновленный Microsoft Store
Магазин Windows нуждается в обновлении. Существует не только внутренняя функциональность, которую можно было бы улучшить, но и способность находить новые приложения не так хороша, как могла бы быть.
Microsoft планирует обновить Магазин — но не сообщает когда — с новым пользовательским интерфейсом и более строгим контролем.
«Мы не только предоставим вам больше приложений, чем когда-либо прежде, но и упростим поиск и обнаружение всего контента — приложений, игр, шоу, фильмов с помощью тщательно отобранных историй и коллекций», — заявляет Microsoft.
Microsoft добавляет в Магазин больше приложений, в том числе некоторые из собственных приложений, которые никогда не появлялись до сих пор, например Microsoft Teams и Visual Studio. Также добавляются ключевые сторонние приложения, такие как Disney +, Adobe Creative Cloud, Zoom и Canva.
Более быстрые обновления Windows
Некоторые люди ненавидят обновления Windows. Microsoft обещает помочь улучшить обновления Windows, сделав их меньше и быстрее, предположительно сделав их менее навязчивыми. Исторически сложилось так, что Microsoft добивалась этого, «зная» точно, какой код имеет ваш компьютер, а затем предоставляя именно то, что ему нужно.
DirectStorage и Auto HDR
Microsoft ранее заявляла, что DirectStorage, основа хранилища Xbox Series X, появится на ПК — но не знали, когда. Теперь мы знаем, что он появится в составе Windows 11.
DirectStorage — это Windows API, который будет использоваться для управления тем, что Microsoft называет архитектурой Xbox Velocity. Это подход Microsoft к уменьшению емкости хранилища, необходимой для игры Xbox Series X, к максимально быстрой загрузке игры и ее ресурсов. Теперь дело доходит до Windows.
AutoHDR скоро появится в играх для Windows. Microsoft демонстрирует, как она обрабатывает яркость в компьютерной игре при включении с HDR. Это будет не так хорошо, как «родной» HDR, но будет близко. Ожидали ли мы когда-нибудь AutoHDR на ПК? Конечно. Но он выпускается вместе с Windows 11, что довольно круто.
«При включении на вашем игровом ПК с поддержкой HDR вы автоматически получите потрясающие визуальные эффекты HDR в дополнительных 1000+ играх с DirectX 11 и DirectX 12!» Ханна Фишер, менеджер программы Microsoft, отметила в недавнем сообщении в блоге, объясняющее новую функцию.
Windows 11 S
Упрощенная операционная система Microsoft Windows 10S никогда не пользовалась большим уважением. Изначально Windows 10X от Microsoft была разработана, чтобы быть еще более простой для Chromebook и возможной заменой Windows 10S. Но, полностью отменив Windows 10X, Microsoft будет жить с упрощенной Windows 11 Home в S-режиме.
Голосовой набор с пунктуацией
Да, это восьмой пункт, о котором Microsoft тихо объявила: голосовая диктовка, но с алгоритмической обработкой знаков препинания. Диктовка — это всегда одна из тех вещей, которые немного сложны, но если вы сможете с ней справиться, это поможет сэкономить уйму времени. (Попробуйте сами в Windows 10, набрав WIN + H). Но для русского языка она пока не работает и непонятно, будет ли.
Microsoft тихо удалила из Windows 11 привычные функции. Пользователи скандалят
| ПоделитьсяВ новой сборке Windows 11 исчезло сразу несколько функций панели задач, которые есть в Windows 10, и которым все давно привыкли. В частности, часы больше не умеют отображать время в секундах, а календарь лишился встроенного органайзера. Microsoft не стала скрывать, что убрала все эти функции специально, чем вызвала поток жалоб со стороны пользователей.
Microsoft стремится к упрощению
Компания Microsoft выпустила новую бета-версию Windows 11, в которой перестало работать несколько функций панели задач (таскбара). Как сообщает профильный ресурс WindowsLatest, пользователи поначалу подумали, что это сбой, который в дальнейшем будет устранен. Однако Microsoft заверила их, что эти функции были удалены целенаправленно.
В частности, Microsoft «упростила» встроенный в таскбар календарь. Если в Windows 10 кликнуть по часам в правом нижнем углу, то откроется мини-приложение календаря, где будет дополнительное окно с возможностью добавления событий на нужный день.
Пользователи снова недовольны Windows 11
Другими словами, это своего рода органайзер, встроенный прямо в панель задач и вызываемый по одному щелчку мышкой. Притом добавленные события синхронизировались с другими устройствами, подключенными к тем же профилям Microsoft, Exchange или Outlook.
Microsoft «порезала» календарь
В Windows 11 от органайзера остался только сам календарь, а добавлять события и просматривать их через таскбар больше нельзя. Пользователи спросили у Microsoft, когда она собирается исправить этот недочет, на что она ответила, что «это не баг, это фича».
Вместо привычной многим функции Microsoft предложила использовать виджет календаря. Виджеты – это еще одна особенность Windows 11, перекочевавшая в систему прямиком из Windows Vista 14-летней давности.
Календарь в Windows 11
«В новом интерфейсе виджетов есть опция календаря, которую вы можете использовать для быстрого просмотра своего личного календаря и его событий», – заявили представители софтверного гиганта на пользовательский запрос в Microsoft Feedback Hub, чем вызвали негодование сотен участников программы бета-тестирования.
«Виджет отображает не все мои календари, а также он полон новостей и ненужного материала. Пожалуйста, прежний календарь, поскольку он был ИДЕАЛЬНЫМ», – отметил один пользователь в Microsoft Feedback Hub.
Органайзер в Windows 10
«В виджете календаря отображается только календарь моей учетной записи Microsoft. Я использую и другие календари, и мне нужно видеть их все вместе. Веб-виджеты не так удобны, и я предпочитаю видеть все свои события во всплывающем меню календаря», – написал другой пользователь.
Секунд больше нет
«Разобравшись» календарем, Microsoft решила модернизировать и встроенные в панель задач часы. В Windows 10 при нажатии на часы выводится не только календарь – во всплывающем меню отображается еще и текущее время. Оно выводится в формате «часы:минуты:секунды», что тоже было в системе с момента релиза ее самой первой стабильной сборки.
Таких точных часов в Windows 11 не будет
В Windows 11 отображение секунд не предусмотрено. Вернуть их никак нельзя – Microsoft удалила их на уровне системного реестра.
Даунгрейд панели задач
Сама панель задач как один из основных элементов интерфейса всех современных версий Windows тоже подверглась «обратному тюнингу». Со слов пользователей, опробовавших новую бета-версию Windows 11, в ней больше нет контекстного меню, появляющегося при клике на таскбаре правой кнопкой мыши.
Контекстное меню панели задач в Windows 10 1803
Возможности перетащить файл или программу на панель задач тоже больше нет. Также стараниями Microsoft пользователи больше не смогут разгруппировать элементы и приложения на панели задач.
Число ненужных пользователям изменений в таскбаре Windows 11 растет изо дня в день
Все это добавляется к главному изменению панели задач – переносу кнопки «Пуск» из левого ее угла в самый центр. Это новшество тоже не пришлось по нраву пользователям. CNews писал, что сторонние разработчики начали выпускать специальные приложения, не только возвращающие кнопку стартовое меню на привычное место, но и придающие самому меню такой же вид, каким оно было в Windows 7 других системах.
Немного о Windows 11
Анонс Windows 11 состоялся в конце июня 2021 г., но пользователи получили доступ к ее ранней версии на неделю раньше благодаря «случайной» утечке. Релиз стабильной версии системы запланирован на IV квартал 2021 г.
От конструктора VR-проектов до повышения цифровой грамотности: как в Москве тестируют инновации в образовании
Инновации и стартапыНовая ОС Microsoft имеет завышенные системные требования и не установится на большинстве современных компьютеров. Хакеры нашли способ обхода этих ограничений, но Microsoft пообещала в дальнейшем закрыть все подобные лазейки.
Обновиться до Windows 11 можно при наличии на ПК Windows 10 или Windows 7. В последнем случае это будет грозить пользователю потерей всех без исключения файлов на системном разделе.
Также в системе намеренно усложнен процесс смены браузера по умолчанию со штатного Edge на Chrome, Vivaldi, Firefox и др. Разработчики этих программ высказали Microsoft свое недовольство подобными «нововведениями».
Основные возможности операционных систем. Системное программирование в среде Windows
Читайте также
Обзор операционных систем и ядер
Обзор операционных систем и ядер Из-за неуклонного роста возможностей и не очень качественного построения некоторых современных операционных систем, понятие операционной системы стало несколько неопределенным. Многие пользователи считают, что то, что они видят на
Книги по основам построения операционных систем
Книги по основам построения операционных систем В этих книгах рассмотрены принципы работы операционных систем в объеме учебных курсов. В них описываются основные понятия, алгоритмы и проблемы, связанные с построением высокофункциональных операционных систем, а также
Книги о ядрах других операционных систем
Книги о ядрах других операционных систем Понимать врагов, точнее не врагов, а конкурентов, — никогда не повредит. В этих книгах обсуждаются основы работы и особенности реализации операционных систем, отличных от операционной системы Linux. Смотрите, что у них хорошо, а
11.3. Установка нескольких операционных систем с помощью виртуальной машины
11.3. Установка нескольких операционных систем с помощью виртуальной машины Общие сведения о виртуальных машинахДля запуска нескольких операционных систем на одном компьютере все чаще используются виртуальные машины. Виртуальная машина – это программа, которая
Глава 9 Прошлое и настоящее сетевых операционных систем
Глава 9 Прошлое и настоящее сетевых операционных систем Извечной и зловещей мечтой вирусов является абсолютное мировое господство, и, как ни ужасны методы, коими они в настоящее время пользуются, им нельзя отказать в настойчивости, изобретательности и способности к
Глава 38 Сосуществование операционных систем
Глава 38 Сосуществование операционных систем Как бы мы ни старались, а полностью жить в операционной системе Linux в современном мире не получается. Так сложилась жизнь, что множество программ написаны под операционные системы MS Windows или DOS. И зачастую по тем или иным
2.7.2. Установка других операционных систем после Linux
2.7.2. Установка других операционных систем после Linux При инсталляции MS-DOS и Windows 95/98 ее стандартный загрузчик независимо от вашего желания записывается в Master Boot Record (MBR), а признак активности в таблице разделов ставится на раздел MS-DOS (Windows 95/98). А стандартный загрузчик MS-DOS и Windows
2.4. Установка нескольких операционных систем
2.4. Установка нескольких операционных систем Прежде чем устанавливать несколько операционных систем на одном компьютере, сделайте резервное копирование важной информации, т.к. процесс установки операционной системы включает в себя переразбиение жесткого диска на
3 Контраст: сравнение философии Unix и других операционных систем
3 Контраст: сравнение философии Unix и других операционных систем Если ваша проблема выглядит неприступной, найдите пользователя Unix, который покажет, как ее решить. Информационный бюллетень Дилберта (Dilbert), 3.0, 1994 —Скотт Адаме Способы создания операционных систем, как
3.2. Сравнение операционных систем
3.2. Сравнение операционных систем Логика выбора конструкции Unix становится более очевидной в сравнении с другими операционными системами. Ниже приводится только общий обзор конструкций19.На рис. 3.1. отражены генетические связи между рассматриваемыми операционными
3 Контраст: сравнение философии Unix и других операционных систем
3 Контраст: сравнение философии Unix и других операционных систем Если ваша проблема выглядит неприступной, найдите пользователя Unix, который покажет, как ее решить. Информационный бюллетень Дилберта (Dilbert), 3.0, 1994 —Скотт Адамс Способы создания операционных систем, как
3.2. Сравнение операционных систем
3.2. Сравнение операционных систем Логика выбора конструкции Unix становится более очевидной в сравнении с другими операционными системами. Ниже приводится только общий обзор конструкций[23].На рис. 3.1. отражены генетические связи между рассматриваемыми операционными
Платформы для операционных систем
Платформы для операционных систем Платформы сервера Firebird включают следующие операционные системы, но не ограничиваются только ими.* Linux, FreeBSD и другие варианты ОС UNIX.* Платформы Microsoft Windows, поддерживающие сервисы: NT 4, Windows 2000 (сервер или рабочая станция), XP Professional и Server 2003.
Часть II Базовые возможности программных комплексов интерактивных систем
Часть II Базовые возможности программных комплексов интерактивных систем В части I были рассмотрены следующие вопросы:? обзор основных принципов работы ИД как интегрирующего элемента ИС;? общее и особенное наиболее популярных ИС;? основные шаги подготовки к работе
Исследование программных интерфейсов приложений операционных систем
Исследование программных интерфейсов приложений операционных систем Операционные системы предоставляют или не предоставляют интерфейсы с уровнем сетевых соединений. Рассмотрим несколько операционных систем, для того чтобы определить, как они взаимодействуют с их
Дыроверт операционных систем
Дыроверт операционных систем ОпСистема «Юних» всегда держалась провокационно на фоне войн операционных систем, подобно Русской Армии. Большинство людей знают только ее репутацию, и предположения о ее репутации, по типу как в мультфильме про Дильберта, смутны. Но все
оконных функций SQL | Advanced SQL
Начиная с этого места? Этот урок является частью полного руководства по использованию SQL для анализа данных. Проверьте начало.
В этом уроке мы рассмотрим:
В этом уроке используются данные программы Capital Bikeshare в Вашингтоне, округ Колумбия, которая публикует подробные исторические данные на уровне поездок на своем веб-сайте. Данные были загружены в феврале 2014 года, но ограничены данными, собранными в течение первого квартала 2012 года.Каждая строка представляет собой одну поездку. Большинство полей говорят сами за себя, за исключением rider_type : «Зарегистрировано» означает ежемесячное членство в программе совместного использования поездок, «Случайный» означает, что гонщик купил 3-дневный проездной. Поля start_time и end_time были очищены от своих исходных форм для соответствия форматированию даты SQL — они хранятся в этой таблице как отметки времени.
Введение в оконные функции
ДокументацияPostgreSQL отлично описывает концепцию оконных функций:
Оконная функция выполняет вычисление по набору строк таблицы, которые каким-то образом связаны с текущей строкой.Это сравнимо с типом расчета, который можно выполнить с помощью агрегатной функции. Но в отличие от обычных агрегатных функций, использование оконной функции не приводит к группированию строк в одну строку вывода — строки сохраняют свои отдельные идентификаторы. За кулисами оконная функция может получить доступ не только к текущей строке результата запроса.
Наиболее практичным примером этого является промежуточная сумма:
ВЫБРАТЬ продолжительность_секунды,
SUM (duration_seconds) OVER (ORDER BY start_time) КАК running_total
ИЗ учебника.dc_bikeshare_q1_2012
Вы можете видеть, что приведенный выше запрос создает агрегацию ( running_total ) без использования GROUP BY . Давайте разберем синтаксис и посмотрим, как он работает.
Базовый синтаксис работы с окнами
Первая часть вышеуказанного агрегирования, SUM (duration_seconds) , очень похожа на любую другую агрегацию. Добавление ПЕРЕМЕЩЕНИЕ обозначает его как оконную функцию. Вы можете прочитать приведенную выше агрегацию как «возьмите сумму duration_seconds по для всего набора результатов в порядке start_time .«
Если вы хотите сузить диапазон от всего набора данных до отдельных групп в наборе данных, вы можете использовать PARTITION BY для этого:
ВЫБРАТЬ start_terminal,
duration_seconds,
SUM (duration_seconds) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY start_time)
AS running_total
ИЗ tutorial.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
Вышеупомянутый запрос группирует и упорядочивает запрос по start_terminal .В каждом значении start_terminal он упорядочен по start_time , а промежуточные итоговые суммы по текущей строке и всем предыдущим строкам duration_seconds . Прокрутите вниз, пока значение start_terminal не изменится, и вы заметите, что running_total начинается заново. Вот что происходит, когда вы группируете с помощью PARTITION BY . Если вы по-прежнему сбиты с толку ORDER BY , он просто упорядочивает по указанным столбцам так же, как предложение ORDER BY , за исключением того, что он обрабатывает каждый раздел как отдельный.Он также создает промежуточную сумму - без ORDER BY каждое значение будет просто суммой всех значений duration_seconds в соответствующем start_terminal . Попробуйте выполнить вышеуказанный запрос без ORDER BY , чтобы получить представление:
ВЫБРАТЬ start_terminal,
duration_seconds,
SUM (duration_seconds) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal) AS start_terminal_total
ИЗ tutorial.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
ORDER и PARTITION определяют то, что называется «окном» - упорядоченное подмножество данных, по которым производятся вычисления.
Примечание. В одном запросе нельзя использовать оконные функции и стандартные агрегаты. В частности, вы не можете включать оконные функции в предложение GROUP BY .
Практическая задача
Напишите модификацию запроса для приведенного выше примера запроса, который показывает продолжительность каждой поездки в процентах от общего времени, затраченного гонщиками с каждого start_terminal.
Попробуй это Посмотреть ответОбычные подозреваемые: SUM, COUNT и AVG
При использовании оконных функций вы можете применять те же агрегаты, что и при обычных обстоятельствах - SUM , COUNT и AVG .Самый простой способ понять это - повторно запустить предыдущий пример с некоторыми дополнительными функциями. Сделайте
ВЫБРАТЬ start_terminal,
duration_seconds,
SUM (duration_seconds) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal) AS running_total,
COUNT (duration_seconds) БОЛЕЕ
(РАЗДЕЛЕНИЕ ПО start_terminal) AS running_count,
AVG (duration_seconds) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal) AS running_avg
ИЗ tutorial.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
В качестве альтернативы те же функции с ЗАКАЗАТЬ ПО :
ВЫБРАТЬ start_terminal,
duration_seconds,
SUM (duration_seconds) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY start_time)
AS running_total,
COUNT (duration_seconds) БОЛЕЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY start_time)
AS running_count,
AVG (duration_seconds) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY start_time)
AS running_avg
ИЗ учебника.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
Убедитесь, что вы подключили эти два предыдущих запроса в Mode и выполняете их. Эта следующая практическая задача очень похожа на примеры, поэтому попробуйте изменить приведенный выше код, а не начинать с нуля.
Практическая задача
Напишите запрос, который показывает общую продолжительность поездок на велосипеде (аналогичный последнему примеру), но сгруппированный по end_terminal , а продолжительность поездки отсортирована в порядке убывания.
ЧИСЛО СТРОКИ ()
ROW_NUMBER () делает именно то, на что похоже - отображает номер данной строки. Он начинается с 1 и нумерует строки в соответствии с частью ORDER BY оператора окна. ROW_NUMBER () не требует указывать переменную в круглых скобках:
ВЫБРАТЬ start_terminal,
начальное время,
duration_seconds,
ROW_NUMBER () OVER (ЗАКАЗАТЬ ПО start_time)
AS row_number
ИЗ учебника.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
Использование предложения PARTITION BY позволит вам снова начать отсчет 1 в каждом разделе. Следующий запрос снова запускает счет для каждого терминала:
ВЫБРАТЬ start_terminal,
начальное время,
duration_seconds,
ROW_NUMBER () ВЫШЕ (РАЗДЕЛ НА start_terminal
ЗАКАЗАТЬ ПО start_time)
AS row_number
ИЗ tutorial.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
RANK () и DENSE_RANK ()
RANK () немного отличается от ROW_NUMBER () .Если вы заказываете до start_time , например, может случиться так, что на некоторых терминалах есть поездки с двумя одинаковыми временами начала. В этом случае им присваивается один и тот же ранг, тогда как ROW_NUMBER () дает им разные номера. В следующем запросе вы заметили 4-е и 5-е наблюдения для start_terminal 31000 - им обоим присвоен ранг 4, а следующий результат получает ранг 6:
ВЫБРАТЬ start_terminal,
duration_seconds,
RANK () OVER (РАЗДЕЛЕНИЕ ПО start_terminal
ЗАКАЗАТЬ ПО start_time)
Рейтинг AS
ИЗ учебника.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
Вы также можете использовать DENSE_RANK () вместо RANK () в зависимости от вашего приложения. Представьте себе ситуацию, в которой три записи имеют одинаковое значение. Используя любую команду, все они получат одинаковый ранг. Для этого примера предположим, что это «2». Вот как две команды будут по-разному оценивать следующие результаты:
-
RANK ()присвоит идентичным строкам ранг 2, затем пропустит ранги 3 и 4, поэтому следующий результат будет 5 -
DENSE_RANK ()все равно присвоит всем идентичным строкам ранг 2, но следующая строка будет иметь ранг 3 - никакие ранги не будут пропущены.
Практическая задача
Напишите запрос, который показывает 5 самых длинных поездок от каждого стартового терминала, упорядоченных по терминалам, и самые длинные поездки в пределах каждого терминала. Ограничены поездками, совершенными до 8 января 2012 года.
Попробуй это Посмотреть ответNTILE
Вы можете использовать оконные функции, чтобы определить, в какой процентиль (или квартиль, или любое другое подразделение) попадает данная строка. Синтаксис: NTILE (* # сегментов *) .В этом случае ORDER BY определяет, какой столбец использовать для определения квартилей (или любого количества плиток, которое вы укажете). Например:
ВЫБРАТЬ start_terminal,
duration_seconds,
NTILE (4) НАД
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
Квартиль AS,
NTILE (5) НАД
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
Квинтиль AS,
NTILE (100) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
Процентиль AS
ИЗ учебника.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
ЗАКАЗАТЬ ПО start_terminal, duration_seconds
Глядя на результаты вышеприведенного запроса, можно увидеть, что столбец процентиль рассчитывается не так, как вы могли ожидать. Если бы у вас было только две записи и вы измеряли процентили, можно было бы ожидать, что одна запись будет определять 1-й процентиль, а другая - 100-й процентиль. Используя функцию NTILE , вы фактически увидите одну запись в 1-м процентиле и одну во 2-м процентиле.Вы можете увидеть это в результатах для start_terminal 31000 - столбец процентиль выглядит как числовое ранжирование. Если вы прокрутите вниз до start_terminal 31007, вы увидите, что процентили вычисляются правильно, поскольку для этого start_terminal имеется более 100 записей. Если вы работаете с очень маленькими окнами, помните об этом и подумайте об использовании квартилей или аналогичных небольших полос.
Практическая задача
Напишите запрос, который показывает только продолжительность поездки и процентиль, в которую она попадает (по всему набору данных, а не по терминалам).
Попробуй это Посмотреть ответLAG и LEAD
Часто бывает полезно сравнить строки с предыдущими или последующими строками, особенно если у вас есть данные в разумном порядке. Вы можете использовать LAG или LEAD для создания столбцов, которые извлекают значения из других строк - все, что вам нужно сделать, это указать, из какого столбца извлекать и сколько строк вы хотите извлекать. LAG тянет из предыдущих рядов, а LEAD тянет из следующих рядов:
ВЫБРАТЬ start_terminal,
duration_seconds,
LAG (duration_seconds; 1) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds) AS отставание,
LEAD (duration_seconds; 1) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds) КАК ведущий
ИЗ учебника.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
ЗАКАЗАТЬ ПО start_terminal, duration_seconds
Это особенно полезно, если вы хотите вычислить разницу между строками:
ВЫБРАТЬ start_terminal,
duration_seconds,
duration_seconds -LAG (duration_seconds, 1) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
Как разница
ИЗ tutorial.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
ЗАКАЗАТЬ ПО start_terminal, duration_seconds
Первая строка столбца разницы пуста, потому что нет предыдущей строки, из которой можно было бы извлечь.Аналогично, использование LEAD создаст нули в конце набора данных. Если вы хотите сделать результаты немного чище, вы можете заключить его во внешний запрос, чтобы удалить нули:
ВЫБРАТЬ *
ИЗ (
ВЫБЕРИТЕ start_terminal,
duration_seconds,
duration_seconds -LAG (duration_seconds, 1) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
Как разница
ИЗ tutorial.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
ЗАКАЗАТЬ ПО start_terminal, duration_seconds
) sub
ГДЕ под.разница НЕ НУЛЬ
Определение псевдонима окна
Если вы планируете написать несколько оконных функций в одном запросе, используя одно и то же окно, вы можете создать псевдоним. Возьмите пример NTILE выше:
ВЫБРАТЬ start_terminal,
duration_seconds,
NTILE (4) НАД
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
Квартиль AS,
NTILE (5) НАД
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
Квинтиль AS,
NTILE (100) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
Процентиль AS
ИЗ учебника.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
ЗАКАЗАТЬ ПО start_terminal, duration_seconds
Это можно переписать как:
ВЫБРАТЬ start_terminal,
duration_seconds,
NTILE (4) OVER ntile_window AS,
NTILE (5) OVER ntile_window AS, квинтиль,
NTILE (100) OVER ntile_window AS процентиль
ИЗ tutorial.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
ОКНО ntile_window AS
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
ЗАКАЗАТЬ ПО start_terminal, duration_seconds
Предложение WINDOW , если оно есть, всегда должно стоять после предложения WHERE .
Расширенные методы работы с окнами
Вы можете проверить полный список оконных функций в Postgres (синтаксис, используемый Mode) в документации Postgres. Если вы используете оконные функции в подключенной базе данных, вам следует изучить соответствующее руководство по синтаксису для вашей системы.
Функции окна SQL | Advanced SQL
Начиная с этого места? Этот урок является частью полного руководства по использованию SQL для анализа данных. Проверьте начало.
В этом уроке мы рассмотрим:
В этом уроке используются данные программы Capital Bikeshare в Вашингтоне, округ Колумбия, которая публикует подробные исторические данные на уровне поездок на своем веб-сайте.Данные были загружены в феврале 2014 года, но ограничены данными, собранными в течение первого квартала 2012 года. Каждая строка представляет собой одну поездку. Большинство полей говорят сами за себя, за исключением rider_type : «Зарегистрировано» означает ежемесячное членство в программе совместного использования поездок, «Случайный» означает, что гонщик купил 3-дневный проездной. Поля start_time и end_time были очищены от своих исходных форм для соответствия форматированию даты SQL - они хранятся в этой таблице как отметки времени.
Введение в оконные функции
ДокументацияPostgreSQL отлично описывает концепцию оконных функций:
Оконная функция выполняет вычисление по набору строк таблицы, которые каким-то образом связаны с текущей строкой. Это сравнимо с типом расчета, который можно выполнить с помощью агрегатной функции. Но в отличие от обычных агрегатных функций, использование оконной функции не приводит к группированию строк в одну строку вывода - строки сохраняют свои отдельные идентификаторы.За кулисами оконная функция может получить доступ не только к текущей строке результата запроса.
Наиболее практичным примером этого является промежуточная сумма:
ВЫБРАТЬ продолжительность_секунды,
SUM (duration_seconds) OVER (ORDER BY start_time) КАК running_total
ИЗ tutorial.dc_bikeshare_q1_2012
Вы можете видеть, что приведенный выше запрос создает агрегацию ( running_total ) без использования GROUP BY . Давайте разберем синтаксис и посмотрим, как он работает.
Базовый синтаксис работы с окнами
Первая часть вышеуказанного агрегирования, SUM (duration_seconds) , очень похожа на любую другую агрегацию. Добавление ПЕРЕМЕЩЕНИЕ обозначает его как оконную функцию. Вы можете прочитать приведенную выше агрегацию как «возьмите сумму duration_seconds по всему набору результатов в порядке start_time ».
Если вы хотите сузить диапазон от всего набора данных до отдельных групп в наборе данных, вы можете использовать PARTITION BY для этого:
ВЫБРАТЬ start_terminal,
duration_seconds,
SUM (duration_seconds) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY start_time)
AS running_total
ИЗ учебника.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
Вышеупомянутый запрос группирует и упорядочивает запрос по start_terminal . В каждом значении start_terminal он упорядочен по start_time , а промежуточные итоговые суммы по текущей строке и всем предыдущим строкам duration_seconds . Прокрутите вниз, пока значение start_terminal не изменится, и вы заметите, что running_total начинается заново. Вот что происходит, когда вы группируете с помощью PARTITION BY .Если вы по-прежнему сбиты с толку ORDER BY , он просто упорядочивает по указанным столбцам так же, как предложение ORDER BY , за исключением того, что он обрабатывает каждый раздел как отдельный. Он также создает промежуточную сумму - без ORDER BY каждое значение будет просто суммой всех значений duration_seconds в соответствующем start_terminal . Попробуйте выполнить вышеуказанный запрос без ORDER BY , чтобы получить представление:
ВЫБРАТЬ start_terminal,
duration_seconds,
SUM (duration_seconds) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal) AS start_terminal_total
ИЗ учебника.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
ORDER и PARTITION определяют то, что называется «окном» - упорядоченное подмножество данных, по которым производятся вычисления.
Примечание. В одном запросе нельзя использовать оконные функции и стандартные агрегаты. В частности, вы не можете включать оконные функции в предложение GROUP BY .
Практическая задача
Напишите модификацию запроса для приведенного выше примера запроса, который показывает продолжительность каждой поездки в процентах от общего времени, затраченного гонщиками с каждого start_terminal.
Попробуй это Посмотреть ответОбычные подозреваемые: SUM, COUNT и AVG
При использовании оконных функций вы можете применять те же агрегаты, что и при обычных обстоятельствах - SUM , COUNT и AVG .Самый простой способ понять это - повторно запустить предыдущий пример с некоторыми дополнительными функциями. Сделайте
ВЫБРАТЬ start_terminal,
duration_seconds,
SUM (duration_seconds) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal) AS running_total,
COUNT (duration_seconds) БОЛЕЕ
(РАЗДЕЛЕНИЕ ПО start_terminal) AS running_count,
AVG (duration_seconds) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal) AS running_avg
ИЗ tutorial.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
В качестве альтернативы те же функции с ЗАКАЗАТЬ ПО :
ВЫБРАТЬ start_terminal,
duration_seconds,
SUM (duration_seconds) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY start_time)
AS running_total,
COUNT (duration_seconds) БОЛЕЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY start_time)
AS running_count,
AVG (duration_seconds) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY start_time)
AS running_avg
ИЗ учебника.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
Убедитесь, что вы подключили эти два предыдущих запроса в Mode и выполняете их. Эта следующая практическая задача очень похожа на примеры, поэтому попробуйте изменить приведенный выше код, а не начинать с нуля.
Практическая задача
Напишите запрос, который показывает общую продолжительность поездок на велосипеде (аналогичный последнему примеру), но сгруппированный по end_terminal , а продолжительность поездки отсортирована в порядке убывания.
ЧИСЛО СТРОКИ ()
ROW_NUMBER () делает именно то, на что похоже - отображает номер данной строки. Он начинается с 1 и нумерует строки в соответствии с частью ORDER BY оператора окна. ROW_NUMBER () не требует указывать переменную в круглых скобках:
ВЫБРАТЬ start_terminal,
начальное время,
duration_seconds,
ROW_NUMBER () OVER (ЗАКАЗАТЬ ПО start_time)
AS row_number
ИЗ учебника.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
Использование предложения PARTITION BY позволит вам снова начать отсчет 1 в каждом разделе. Следующий запрос снова запускает счет для каждого терминала:
ВЫБРАТЬ start_terminal,
начальное время,
duration_seconds,
ROW_NUMBER () ВЫШЕ (РАЗДЕЛ НА start_terminal
ЗАКАЗАТЬ ПО start_time)
AS row_number
ИЗ tutorial.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
RANK () и DENSE_RANK ()
RANK () немного отличается от ROW_NUMBER () .Если вы заказываете до start_time , например, может случиться так, что на некоторых терминалах есть поездки с двумя одинаковыми временами начала. В этом случае им присваивается один и тот же ранг, тогда как ROW_NUMBER () дает им разные номера. В следующем запросе вы заметили 4-е и 5-е наблюдения для start_terminal 31000 - им обоим присвоен ранг 4, а следующий результат получает ранг 6:
ВЫБРАТЬ start_terminal,
duration_seconds,
RANK () OVER (РАЗДЕЛЕНИЕ ПО start_terminal
ЗАКАЗАТЬ ПО start_time)
Рейтинг AS
ИЗ учебника.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
Вы также можете использовать DENSE_RANK () вместо RANK () в зависимости от вашего приложения. Представьте себе ситуацию, в которой три записи имеют одинаковое значение. Используя любую команду, все они получат одинаковый ранг. Для этого примера предположим, что это «2». Вот как две команды будут по-разному оценивать следующие результаты:
-
RANK ()присвоит идентичным строкам ранг 2, затем пропустит ранги 3 и 4, поэтому следующий результат будет 5 -
DENSE_RANK ()все равно присвоит всем идентичным строкам ранг 2, но следующая строка будет иметь ранг 3 - никакие ранги не будут пропущены.
Практическая задача
Напишите запрос, который показывает 5 самых длинных поездок от каждого стартового терминала, упорядоченных по терминалам, и самые длинные поездки в пределах каждого терминала. Ограничены поездками, совершенными до 8 января 2012 года.
Попробуй это Посмотреть ответNTILE
Вы можете использовать оконные функции, чтобы определить, в какой процентиль (или квартиль, или любое другое подразделение) попадает данная строка. Синтаксис: NTILE (* # сегментов *) .В этом случае ORDER BY определяет, какой столбец использовать для определения квартилей (или любого количества плиток, которое вы укажете). Например:
ВЫБРАТЬ start_terminal,
duration_seconds,
NTILE (4) НАД
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
Квартиль AS,
NTILE (5) НАД
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
Квинтиль AS,
NTILE (100) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
Процентиль AS
ИЗ учебника.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
ЗАКАЗАТЬ ПО start_terminal, duration_seconds
Глядя на результаты вышеприведенного запроса, можно увидеть, что столбец процентиль рассчитывается не так, как вы могли ожидать. Если бы у вас было только две записи и вы измеряли процентили, можно было бы ожидать, что одна запись будет определять 1-й процентиль, а другая - 100-й процентиль. Используя функцию NTILE , вы фактически увидите одну запись в 1-м процентиле и одну во 2-м процентиле.Вы можете увидеть это в результатах для start_terminal 31000 - столбец процентиль выглядит как числовое ранжирование. Если вы прокрутите вниз до start_terminal 31007, вы увидите, что процентили вычисляются правильно, поскольку для этого start_terminal имеется более 100 записей. Если вы работаете с очень маленькими окнами, помните об этом и подумайте об использовании квартилей или аналогичных небольших полос.
Практическая задача
Напишите запрос, который показывает только продолжительность поездки и процентиль, в которую она попадает (по всему набору данных, а не по терминалам).
Попробуй это Посмотреть ответLAG и LEAD
Часто бывает полезно сравнить строки с предыдущими или последующими строками, особенно если у вас есть данные в разумном порядке. Вы можете использовать LAG или LEAD для создания столбцов, которые извлекают значения из других строк - все, что вам нужно сделать, это указать, из какого столбца извлекать и сколько строк вы хотите извлекать. LAG тянет из предыдущих рядов, а LEAD тянет из следующих рядов:
ВЫБРАТЬ start_terminal,
duration_seconds,
LAG (duration_seconds; 1) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds) AS отставание,
LEAD (duration_seconds; 1) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds) КАК ведущий
ИЗ учебника.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
ЗАКАЗАТЬ ПО start_terminal, duration_seconds
Это особенно полезно, если вы хотите вычислить разницу между строками:
ВЫБРАТЬ start_terminal,
duration_seconds,
duration_seconds -LAG (duration_seconds, 1) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
Как разница
ИЗ tutorial.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
ЗАКАЗАТЬ ПО start_terminal, duration_seconds
Первая строка столбца разницы пуста, потому что нет предыдущей строки, из которой можно было бы извлечь.Аналогично, использование LEAD создаст нули в конце набора данных. Если вы хотите сделать результаты немного чище, вы можете заключить его во внешний запрос, чтобы удалить нули:
ВЫБРАТЬ *
ИЗ (
ВЫБЕРИТЕ start_terminal,
duration_seconds,
duration_seconds -LAG (duration_seconds, 1) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
Как разница
ИЗ tutorial.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
ЗАКАЗАТЬ ПО start_terminal, duration_seconds
) sub
ГДЕ под.разница НЕ НУЛЬ
Определение псевдонима окна
Если вы планируете написать несколько оконных функций в одном запросе, используя одно и то же окно, вы можете создать псевдоним. Возьмите пример NTILE выше:
ВЫБРАТЬ start_terminal,
duration_seconds,
NTILE (4) НАД
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
Квартиль AS,
NTILE (5) НАД
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
Квинтиль AS,
NTILE (100) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
Процентиль AS
ИЗ учебника.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
ЗАКАЗАТЬ ПО start_terminal, duration_seconds
Это можно переписать как:
ВЫБРАТЬ start_terminal,
duration_seconds,
NTILE (4) OVER ntile_window AS,
NTILE (5) OVER ntile_window AS, квинтиль,
NTILE (100) OVER ntile_window AS процентиль
ИЗ tutorial.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
ОКНО ntile_window AS
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
ЗАКАЗАТЬ ПО start_terminal, duration_seconds
Предложение WINDOW , если оно есть, всегда должно стоять после предложения WHERE .
Расширенные методы работы с окнами
Вы можете проверить полный список оконных функций в Postgres (синтаксис, используемый Mode) в документации Postgres. Если вы используете оконные функции в подключенной базе данных, вам следует изучить соответствующее руководство по синтаксису для вашей системы.
Функции окна SQL | Advanced SQL
Начиная с этого места? Этот урок является частью полного руководства по использованию SQL для анализа данных. Проверьте начало.
В этом уроке мы рассмотрим:
В этом уроке используются данные программы Capital Bikeshare в Вашингтоне, округ Колумбия, которая публикует подробные исторические данные на уровне поездок на своем веб-сайте.Данные были загружены в феврале 2014 года, но ограничены данными, собранными в течение первого квартала 2012 года. Каждая строка представляет собой одну поездку. Большинство полей говорят сами за себя, за исключением rider_type : «Зарегистрировано» означает ежемесячное членство в программе совместного использования поездок, «Случайный» означает, что гонщик купил 3-дневный проездной. Поля start_time и end_time были очищены от своих исходных форм для соответствия форматированию даты SQL - они хранятся в этой таблице как отметки времени.
Введение в оконные функции
ДокументацияPostgreSQL отлично описывает концепцию оконных функций:
Оконная функция выполняет вычисление по набору строк таблицы, которые каким-то образом связаны с текущей строкой. Это сравнимо с типом расчета, который можно выполнить с помощью агрегатной функции. Но в отличие от обычных агрегатных функций, использование оконной функции не приводит к группированию строк в одну строку вывода - строки сохраняют свои отдельные идентификаторы.За кулисами оконная функция может получить доступ не только к текущей строке результата запроса.
Наиболее практичным примером этого является промежуточная сумма:
ВЫБРАТЬ продолжительность_секунды,
SUM (duration_seconds) OVER (ORDER BY start_time) КАК running_total
ИЗ tutorial.dc_bikeshare_q1_2012
Вы можете видеть, что приведенный выше запрос создает агрегацию ( running_total ) без использования GROUP BY . Давайте разберем синтаксис и посмотрим, как он работает.
Базовый синтаксис работы с окнами
Первая часть вышеуказанного агрегирования, SUM (duration_seconds) , очень похожа на любую другую агрегацию. Добавление ПЕРЕМЕЩЕНИЕ обозначает его как оконную функцию. Вы можете прочитать приведенную выше агрегацию как «возьмите сумму duration_seconds по всему набору результатов в порядке start_time ».
Если вы хотите сузить диапазон от всего набора данных до отдельных групп в наборе данных, вы можете использовать PARTITION BY для этого:
ВЫБРАТЬ start_terminal,
duration_seconds,
SUM (duration_seconds) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY start_time)
AS running_total
ИЗ учебника.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
Вышеупомянутый запрос группирует и упорядочивает запрос по start_terminal . В каждом значении start_terminal он упорядочен по start_time , а промежуточные итоговые суммы по текущей строке и всем предыдущим строкам duration_seconds . Прокрутите вниз, пока значение start_terminal не изменится, и вы заметите, что running_total начинается заново. Вот что происходит, когда вы группируете с помощью PARTITION BY .Если вы по-прежнему сбиты с толку ORDER BY , он просто упорядочивает по указанным столбцам так же, как предложение ORDER BY , за исключением того, что он обрабатывает каждый раздел как отдельный. Он также создает промежуточную сумму - без ORDER BY каждое значение будет просто суммой всех значений duration_seconds в соответствующем start_terminal . Попробуйте выполнить вышеуказанный запрос без ORDER BY , чтобы получить представление:
ВЫБРАТЬ start_terminal,
duration_seconds,
SUM (duration_seconds) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal) AS start_terminal_total
ИЗ учебника.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
ORDER и PARTITION определяют то, что называется «окном» - упорядоченное подмножество данных, по которым производятся вычисления.
Примечание. В одном запросе нельзя использовать оконные функции и стандартные агрегаты. В частности, вы не можете включать оконные функции в предложение GROUP BY .
Практическая задача
Напишите модификацию запроса для приведенного выше примера запроса, который показывает продолжительность каждой поездки в процентах от общего времени, затраченного гонщиками с каждого start_terminal.
Попробуй это Посмотреть ответОбычные подозреваемые: SUM, COUNT и AVG
При использовании оконных функций вы можете применять те же агрегаты, что и при обычных обстоятельствах - SUM , COUNT и AVG .Самый простой способ понять это - повторно запустить предыдущий пример с некоторыми дополнительными функциями. Сделайте
ВЫБРАТЬ start_terminal,
duration_seconds,
SUM (duration_seconds) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal) AS running_total,
COUNT (duration_seconds) БОЛЕЕ
(РАЗДЕЛЕНИЕ ПО start_terminal) AS running_count,
AVG (duration_seconds) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal) AS running_avg
ИЗ tutorial.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
В качестве альтернативы те же функции с ЗАКАЗАТЬ ПО :
ВЫБРАТЬ start_terminal,
duration_seconds,
SUM (duration_seconds) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY start_time)
AS running_total,
COUNT (duration_seconds) БОЛЕЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY start_time)
AS running_count,
AVG (duration_seconds) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY start_time)
AS running_avg
ИЗ учебника.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
Убедитесь, что вы подключили эти два предыдущих запроса в Mode и выполняете их. Эта следующая практическая задача очень похожа на примеры, поэтому попробуйте изменить приведенный выше код, а не начинать с нуля.
Практическая задача
Напишите запрос, который показывает общую продолжительность поездок на велосипеде (аналогичный последнему примеру), но сгруппированный по end_terminal , а продолжительность поездки отсортирована в порядке убывания.
ЧИСЛО СТРОКИ ()
ROW_NUMBER () делает именно то, на что похоже - отображает номер данной строки. Он начинается с 1 и нумерует строки в соответствии с частью ORDER BY оператора окна. ROW_NUMBER () не требует указывать переменную в круглых скобках:
ВЫБРАТЬ start_terminal,
начальное время,
duration_seconds,
ROW_NUMBER () OVER (ЗАКАЗАТЬ ПО start_time)
AS row_number
ИЗ учебника.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
Использование предложения PARTITION BY позволит вам снова начать отсчет 1 в каждом разделе. Следующий запрос снова запускает счет для каждого терминала:
ВЫБРАТЬ start_terminal,
начальное время,
duration_seconds,
ROW_NUMBER () ВЫШЕ (РАЗДЕЛ НА start_terminal
ЗАКАЗАТЬ ПО start_time)
AS row_number
ИЗ tutorial.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
RANK () и DENSE_RANK ()
RANK () немного отличается от ROW_NUMBER () .Если вы заказываете до start_time , например, может случиться так, что на некоторых терминалах есть поездки с двумя одинаковыми временами начала. В этом случае им присваивается один и тот же ранг, тогда как ROW_NUMBER () дает им разные номера. В следующем запросе вы заметили 4-е и 5-е наблюдения для start_terminal 31000 - им обоим присвоен ранг 4, а следующий результат получает ранг 6:
ВЫБРАТЬ start_terminal,
duration_seconds,
RANK () OVER (РАЗДЕЛЕНИЕ ПО start_terminal
ЗАКАЗАТЬ ПО start_time)
Рейтинг AS
ИЗ учебника.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
Вы также можете использовать DENSE_RANK () вместо RANK () в зависимости от вашего приложения. Представьте себе ситуацию, в которой три записи имеют одинаковое значение. Используя любую команду, все они получат одинаковый ранг. Для этого примера предположим, что это «2». Вот как две команды будут по-разному оценивать следующие результаты:
-
RANK ()присвоит идентичным строкам ранг 2, затем пропустит ранги 3 и 4, поэтому следующий результат будет 5 -
DENSE_RANK ()все равно присвоит всем идентичным строкам ранг 2, но следующая строка будет иметь ранг 3 - никакие ранги не будут пропущены.
Практическая задача
Напишите запрос, который показывает 5 самых длинных поездок от каждого стартового терминала, упорядоченных по терминалам, и самые длинные поездки в пределах каждого терминала. Ограничены поездками, совершенными до 8 января 2012 года.
Попробуй это Посмотреть ответNTILE
Вы можете использовать оконные функции, чтобы определить, в какой процентиль (или квартиль, или любое другое подразделение) попадает данная строка. Синтаксис: NTILE (* # сегментов *) .В этом случае ORDER BY определяет, какой столбец использовать для определения квартилей (или любого количества плиток, которое вы укажете). Например:
ВЫБРАТЬ start_terminal,
duration_seconds,
NTILE (4) НАД
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
Квартиль AS,
NTILE (5) НАД
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
Квинтиль AS,
NTILE (100) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
Процентиль AS
ИЗ учебника.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
ЗАКАЗАТЬ ПО start_terminal, duration_seconds
Глядя на результаты вышеприведенного запроса, можно увидеть, что столбец процентиль рассчитывается не так, как вы могли ожидать. Если бы у вас было только две записи и вы измеряли процентили, можно было бы ожидать, что одна запись будет определять 1-й процентиль, а другая - 100-й процентиль. Используя функцию NTILE , вы фактически увидите одну запись в 1-м процентиле и одну во 2-м процентиле.Вы можете увидеть это в результатах для start_terminal 31000 - столбец процентиль выглядит как числовое ранжирование. Если вы прокрутите вниз до start_terminal 31007, вы увидите, что процентили вычисляются правильно, поскольку для этого start_terminal имеется более 100 записей. Если вы работаете с очень маленькими окнами, помните об этом и подумайте об использовании квартилей или аналогичных небольших полос.
Практическая задача
Напишите запрос, который показывает только продолжительность поездки и процентиль, в которую она попадает (по всему набору данных, а не по терминалам).
Попробуй это Посмотреть ответLAG и LEAD
Часто бывает полезно сравнить строки с предыдущими или последующими строками, особенно если у вас есть данные в разумном порядке. Вы можете использовать LAG или LEAD для создания столбцов, которые извлекают значения из других строк - все, что вам нужно сделать, это указать, из какого столбца извлекать и сколько строк вы хотите извлекать. LAG тянет из предыдущих рядов, а LEAD тянет из следующих рядов:
ВЫБРАТЬ start_terminal,
duration_seconds,
LAG (duration_seconds; 1) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds) AS отставание,
LEAD (duration_seconds; 1) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds) КАК ведущий
ИЗ учебника.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
ЗАКАЗАТЬ ПО start_terminal, duration_seconds
Это особенно полезно, если вы хотите вычислить разницу между строками:
ВЫБРАТЬ start_terminal,
duration_seconds,
duration_seconds -LAG (duration_seconds, 1) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
Как разница
ИЗ tutorial.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
ЗАКАЗАТЬ ПО start_terminal, duration_seconds
Первая строка столбца разницы пуста, потому что нет предыдущей строки, из которой можно было бы извлечь.Аналогично, использование LEAD создаст нули в конце набора данных. Если вы хотите сделать результаты немного чище, вы можете заключить его во внешний запрос, чтобы удалить нули:
ВЫБРАТЬ *
ИЗ (
ВЫБЕРИТЕ start_terminal,
duration_seconds,
duration_seconds -LAG (duration_seconds, 1) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
Как разница
ИЗ tutorial.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
ЗАКАЗАТЬ ПО start_terminal, duration_seconds
) sub
ГДЕ под.разница НЕ НУЛЬ
Определение псевдонима окна
Если вы планируете написать несколько оконных функций в одном запросе, используя одно и то же окно, вы можете создать псевдоним. Возьмите пример NTILE выше:
ВЫБРАТЬ start_terminal,
duration_seconds,
NTILE (4) НАД
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
Квартиль AS,
NTILE (5) НАД
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
Квинтиль AS,
NTILE (100) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
Процентиль AS
ИЗ учебника.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
ЗАКАЗАТЬ ПО start_terminal, duration_seconds
Это можно переписать как:
ВЫБРАТЬ start_terminal,
duration_seconds,
NTILE (4) OVER ntile_window AS,
NTILE (5) OVER ntile_window AS, квинтиль,
NTILE (100) OVER ntile_window AS процентиль
ИЗ tutorial.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
ОКНО ntile_window AS
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
ЗАКАЗАТЬ ПО start_terminal, duration_seconds
Предложение WINDOW , если оно есть, всегда должно стоять после предложения WHERE .
Расширенные методы работы с окнами
Вы можете проверить полный список оконных функций в Postgres (синтаксис, используемый Mode) в документации Postgres. Если вы используете оконные функции в подключенной базе данных, вам следует изучить соответствующее руководство по синтаксису для вашей системы.
Функции окна SQL | Advanced SQL
Начиная с этого места? Этот урок является частью полного руководства по использованию SQL для анализа данных. Проверьте начало.
В этом уроке мы рассмотрим:
В этом уроке используются данные программы Capital Bikeshare в Вашингтоне, округ Колумбия, которая публикует подробные исторические данные на уровне поездок на своем веб-сайте.Данные были загружены в феврале 2014 года, но ограничены данными, собранными в течение первого квартала 2012 года. Каждая строка представляет собой одну поездку. Большинство полей говорят сами за себя, за исключением rider_type : «Зарегистрировано» означает ежемесячное членство в программе совместного использования поездок, «Случайный» означает, что гонщик купил 3-дневный проездной. Поля start_time и end_time были очищены от своих исходных форм для соответствия форматированию даты SQL - они хранятся в этой таблице как отметки времени.
Введение в оконные функции
ДокументацияPostgreSQL отлично описывает концепцию оконных функций:
Оконная функция выполняет вычисление по набору строк таблицы, которые каким-то образом связаны с текущей строкой. Это сравнимо с типом расчета, который можно выполнить с помощью агрегатной функции. Но в отличие от обычных агрегатных функций, использование оконной функции не приводит к группированию строк в одну строку вывода - строки сохраняют свои отдельные идентификаторы.За кулисами оконная функция может получить доступ не только к текущей строке результата запроса.
Наиболее практичным примером этого является промежуточная сумма:
ВЫБРАТЬ продолжительность_секунды,
SUM (duration_seconds) OVER (ORDER BY start_time) КАК running_total
ИЗ tutorial.dc_bikeshare_q1_2012
Вы можете видеть, что приведенный выше запрос создает агрегацию ( running_total ) без использования GROUP BY . Давайте разберем синтаксис и посмотрим, как он работает.
Базовый синтаксис работы с окнами
Первая часть вышеуказанного агрегирования, SUM (duration_seconds) , очень похожа на любую другую агрегацию. Добавление ПЕРЕМЕЩЕНИЕ обозначает его как оконную функцию. Вы можете прочитать приведенную выше агрегацию как «возьмите сумму duration_seconds по всему набору результатов в порядке start_time ».
Если вы хотите сузить диапазон от всего набора данных до отдельных групп в наборе данных, вы можете использовать PARTITION BY для этого:
ВЫБРАТЬ start_terminal,
duration_seconds,
SUM (duration_seconds) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY start_time)
AS running_total
ИЗ учебника.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
Вышеупомянутый запрос группирует и упорядочивает запрос по start_terminal . В каждом значении start_terminal он упорядочен по start_time , а промежуточные итоговые суммы по текущей строке и всем предыдущим строкам duration_seconds . Прокрутите вниз, пока значение start_terminal не изменится, и вы заметите, что running_total начинается заново. Вот что происходит, когда вы группируете с помощью PARTITION BY .Если вы по-прежнему сбиты с толку ORDER BY , он просто упорядочивает по указанным столбцам так же, как предложение ORDER BY , за исключением того, что он обрабатывает каждый раздел как отдельный. Он также создает промежуточную сумму - без ORDER BY каждое значение будет просто суммой всех значений duration_seconds в соответствующем start_terminal . Попробуйте выполнить вышеуказанный запрос без ORDER BY , чтобы получить представление:
ВЫБРАТЬ start_terminal,
duration_seconds,
SUM (duration_seconds) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal) AS start_terminal_total
ИЗ учебника.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
ORDER и PARTITION определяют то, что называется «окном» - упорядоченное подмножество данных, по которым производятся вычисления.
Примечание. В одном запросе нельзя использовать оконные функции и стандартные агрегаты. В частности, вы не можете включать оконные функции в предложение GROUP BY .
Практическая задача
Напишите модификацию запроса для приведенного выше примера запроса, который показывает продолжительность каждой поездки в процентах от общего времени, затраченного гонщиками с каждого start_terminal.
Попробуй это Посмотреть ответОбычные подозреваемые: SUM, COUNT и AVG
При использовании оконных функций вы можете применять те же агрегаты, что и при обычных обстоятельствах - SUM , COUNT и AVG .Самый простой способ понять это - повторно запустить предыдущий пример с некоторыми дополнительными функциями. Сделайте
ВЫБРАТЬ start_terminal,
duration_seconds,
SUM (duration_seconds) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal) AS running_total,
COUNT (duration_seconds) БОЛЕЕ
(РАЗДЕЛЕНИЕ ПО start_terminal) AS running_count,
AVG (duration_seconds) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal) AS running_avg
ИЗ tutorial.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
В качестве альтернативы те же функции с ЗАКАЗАТЬ ПО :
ВЫБРАТЬ start_terminal,
duration_seconds,
SUM (duration_seconds) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY start_time)
AS running_total,
COUNT (duration_seconds) БОЛЕЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY start_time)
AS running_count,
AVG (duration_seconds) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY start_time)
AS running_avg
ИЗ учебника.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
Убедитесь, что вы подключили эти два предыдущих запроса в Mode и выполняете их. Эта следующая практическая задача очень похожа на примеры, поэтому попробуйте изменить приведенный выше код, а не начинать с нуля.
Практическая задача
Напишите запрос, который показывает общую продолжительность поездок на велосипеде (аналогичный последнему примеру), но сгруппированный по end_terminal , а продолжительность поездки отсортирована в порядке убывания.
ЧИСЛО СТРОКИ ()
ROW_NUMBER () делает именно то, на что похоже - отображает номер данной строки. Он начинается с 1 и нумерует строки в соответствии с частью ORDER BY оператора окна. ROW_NUMBER () не требует указывать переменную в круглых скобках:
ВЫБРАТЬ start_terminal,
начальное время,
duration_seconds,
ROW_NUMBER () OVER (ЗАКАЗАТЬ ПО start_time)
AS row_number
ИЗ учебника.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
Использование предложения PARTITION BY позволит вам снова начать отсчет 1 в каждом разделе. Следующий запрос снова запускает счет для каждого терминала:
ВЫБРАТЬ start_terminal,
начальное время,
duration_seconds,
ROW_NUMBER () ВЫШЕ (РАЗДЕЛ НА start_terminal
ЗАКАЗАТЬ ПО start_time)
AS row_number
ИЗ tutorial.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
RANK () и DENSE_RANK ()
RANK () немного отличается от ROW_NUMBER () .Если вы заказываете до start_time , например, может случиться так, что на некоторых терминалах есть поездки с двумя одинаковыми временами начала. В этом случае им присваивается один и тот же ранг, тогда как ROW_NUMBER () дает им разные номера. В следующем запросе вы заметили 4-е и 5-е наблюдения для start_terminal 31000 - им обоим присвоен ранг 4, а следующий результат получает ранг 6:
ВЫБРАТЬ start_terminal,
duration_seconds,
RANK () OVER (РАЗДЕЛЕНИЕ ПО start_terminal
ЗАКАЗАТЬ ПО start_time)
Рейтинг AS
ИЗ учебника.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
Вы также можете использовать DENSE_RANK () вместо RANK () в зависимости от вашего приложения. Представьте себе ситуацию, в которой три записи имеют одинаковое значение. Используя любую команду, все они получат одинаковый ранг. Для этого примера предположим, что это «2». Вот как две команды будут по-разному оценивать следующие результаты:
-
RANK ()присвоит идентичным строкам ранг 2, затем пропустит ранги 3 и 4, поэтому следующий результат будет 5 -
DENSE_RANK ()все равно присвоит всем идентичным строкам ранг 2, но следующая строка будет иметь ранг 3 - никакие ранги не будут пропущены.
Практическая задача
Напишите запрос, который показывает 5 самых длинных поездок от каждого стартового терминала, упорядоченных по терминалам, и самые длинные поездки в пределах каждого терминала. Ограничены поездками, совершенными до 8 января 2012 года.
Попробуй это Посмотреть ответNTILE
Вы можете использовать оконные функции, чтобы определить, в какой процентиль (или квартиль, или любое другое подразделение) попадает данная строка. Синтаксис: NTILE (* # сегментов *) .В этом случае ORDER BY определяет, какой столбец использовать для определения квартилей (или любого количества плиток, которое вы укажете). Например:
ВЫБРАТЬ start_terminal,
duration_seconds,
NTILE (4) НАД
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
Квартиль AS,
NTILE (5) НАД
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
Квинтиль AS,
NTILE (100) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
Процентиль AS
ИЗ учебника.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
ЗАКАЗАТЬ ПО start_terminal, duration_seconds
Глядя на результаты вышеприведенного запроса, можно увидеть, что столбец процентиль рассчитывается не так, как вы могли ожидать. Если бы у вас было только две записи и вы измеряли процентили, можно было бы ожидать, что одна запись будет определять 1-й процентиль, а другая - 100-й процентиль. Используя функцию NTILE , вы фактически увидите одну запись в 1-м процентиле и одну во 2-м процентиле.Вы можете увидеть это в результатах для start_terminal 31000 - столбец процентиль выглядит как числовое ранжирование. Если вы прокрутите вниз до start_terminal 31007, вы увидите, что процентили вычисляются правильно, поскольку для этого start_terminal имеется более 100 записей. Если вы работаете с очень маленькими окнами, помните об этом и подумайте об использовании квартилей или аналогичных небольших полос.
Практическая задача
Напишите запрос, который показывает только продолжительность поездки и процентиль, в которую она попадает (по всему набору данных, а не по терминалам).
Попробуй это Посмотреть ответLAG и LEAD
Часто бывает полезно сравнить строки с предыдущими или последующими строками, особенно если у вас есть данные в разумном порядке. Вы можете использовать LAG или LEAD для создания столбцов, которые извлекают значения из других строк - все, что вам нужно сделать, это указать, из какого столбца извлекать и сколько строк вы хотите извлекать. LAG тянет из предыдущих рядов, а LEAD тянет из следующих рядов:
ВЫБРАТЬ start_terminal,
duration_seconds,
LAG (duration_seconds; 1) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds) AS отставание,
LEAD (duration_seconds; 1) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds) КАК ведущий
ИЗ учебника.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
ЗАКАЗАТЬ ПО start_terminal, duration_seconds
Это особенно полезно, если вы хотите вычислить разницу между строками:
ВЫБРАТЬ start_terminal,
duration_seconds,
duration_seconds -LAG (duration_seconds, 1) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
Как разница
ИЗ tutorial.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
ЗАКАЗАТЬ ПО start_terminal, duration_seconds
Первая строка столбца разницы пуста, потому что нет предыдущей строки, из которой можно было бы извлечь.Аналогично, использование LEAD создаст нули в конце набора данных. Если вы хотите сделать результаты немного чище, вы можете заключить его во внешний запрос, чтобы удалить нули:
ВЫБРАТЬ *
ИЗ (
ВЫБЕРИТЕ start_terminal,
duration_seconds,
duration_seconds -LAG (duration_seconds, 1) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
Как разница
ИЗ tutorial.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
ЗАКАЗАТЬ ПО start_terminal, duration_seconds
) sub
ГДЕ под.разница НЕ НУЛЬ
Определение псевдонима окна
Если вы планируете написать несколько оконных функций в одном запросе, используя одно и то же окно, вы можете создать псевдоним. Возьмите пример NTILE выше:
ВЫБРАТЬ start_terminal,
duration_seconds,
NTILE (4) НАД
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
Квартиль AS,
NTILE (5) НАД
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
Квинтиль AS,
NTILE (100) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
Процентиль AS
ИЗ учебника.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
ЗАКАЗАТЬ ПО start_terminal, duration_seconds
Это можно переписать как:
ВЫБРАТЬ start_terminal,
duration_seconds,
NTILE (4) OVER ntile_window AS,
NTILE (5) OVER ntile_window AS, квинтиль,
NTILE (100) OVER ntile_window AS процентиль
ИЗ tutorial.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
ОКНО ntile_window AS
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
ЗАКАЗАТЬ ПО start_terminal, duration_seconds
Предложение WINDOW , если оно есть, всегда должно стоять после предложения WHERE .
Расширенные методы работы с окнами
Вы можете проверить полный список оконных функций в Postgres (синтаксис, используемый Mode) в документации Postgres. Если вы используете оконные функции в подключенной базе данных, вам следует изучить соответствующее руководство по синтаксису для вашей системы.
Функции окна SQL | Advanced SQL
Начиная с этого места? Этот урок является частью полного руководства по использованию SQL для анализа данных. Проверьте начало.
В этом уроке мы рассмотрим:
В этом уроке используются данные программы Capital Bikeshare в Вашингтоне, округ Колумбия, которая публикует подробные исторические данные на уровне поездок на своем веб-сайте.Данные были загружены в феврале 2014 года, но ограничены данными, собранными в течение первого квартала 2012 года. Каждая строка представляет собой одну поездку. Большинство полей говорят сами за себя, за исключением rider_type : «Зарегистрировано» означает ежемесячное членство в программе совместного использования поездок, «Случайный» означает, что гонщик купил 3-дневный проездной. Поля start_time и end_time были очищены от своих исходных форм для соответствия форматированию даты SQL - они хранятся в этой таблице как отметки времени.
Введение в оконные функции
ДокументацияPostgreSQL отлично описывает концепцию оконных функций:
Оконная функция выполняет вычисление по набору строк таблицы, которые каким-то образом связаны с текущей строкой. Это сравнимо с типом расчета, который можно выполнить с помощью агрегатной функции. Но в отличие от обычных агрегатных функций, использование оконной функции не приводит к группированию строк в одну строку вывода - строки сохраняют свои отдельные идентификаторы.За кулисами оконная функция может получить доступ не только к текущей строке результата запроса.
Наиболее практичным примером этого является промежуточная сумма:
ВЫБРАТЬ продолжительность_секунды,
SUM (duration_seconds) OVER (ORDER BY start_time) КАК running_total
ИЗ tutorial.dc_bikeshare_q1_2012
Вы можете видеть, что приведенный выше запрос создает агрегацию ( running_total ) без использования GROUP BY . Давайте разберем синтаксис и посмотрим, как он работает.
Базовый синтаксис работы с окнами
Первая часть вышеуказанного агрегирования, SUM (duration_seconds) , очень похожа на любую другую агрегацию. Добавление ПЕРЕМЕЩЕНИЕ обозначает его как оконную функцию. Вы можете прочитать приведенную выше агрегацию как «возьмите сумму duration_seconds по всему набору результатов в порядке start_time ».
Если вы хотите сузить диапазон от всего набора данных до отдельных групп в наборе данных, вы можете использовать PARTITION BY для этого:
ВЫБРАТЬ start_terminal,
duration_seconds,
SUM (duration_seconds) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY start_time)
AS running_total
ИЗ учебника.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
Вышеупомянутый запрос группирует и упорядочивает запрос по start_terminal . В каждом значении start_terminal он упорядочен по start_time , а промежуточные итоговые суммы по текущей строке и всем предыдущим строкам duration_seconds . Прокрутите вниз, пока значение start_terminal не изменится, и вы заметите, что running_total начинается заново. Вот что происходит, когда вы группируете с помощью PARTITION BY .Если вы по-прежнему сбиты с толку ORDER BY , он просто упорядочивает по указанным столбцам так же, как предложение ORDER BY , за исключением того, что он обрабатывает каждый раздел как отдельный. Он также создает промежуточную сумму - без ORDER BY каждое значение будет просто суммой всех значений duration_seconds в соответствующем start_terminal . Попробуйте выполнить вышеуказанный запрос без ORDER BY , чтобы получить представление:
ВЫБРАТЬ start_terminal,
duration_seconds,
SUM (duration_seconds) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal) AS start_terminal_total
ИЗ учебника.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
ORDER и PARTITION определяют то, что называется «окном» - упорядоченное подмножество данных, по которым производятся вычисления.
Примечание. В одном запросе нельзя использовать оконные функции и стандартные агрегаты. В частности, вы не можете включать оконные функции в предложение GROUP BY .
Практическая задача
Напишите модификацию запроса для приведенного выше примера запроса, который показывает продолжительность каждой поездки в процентах от общего времени, затраченного гонщиками с каждого start_terminal.
Попробуй это Посмотреть ответОбычные подозреваемые: SUM, COUNT и AVG
При использовании оконных функций вы можете применять те же агрегаты, что и при обычных обстоятельствах - SUM , COUNT и AVG .Самый простой способ понять это - повторно запустить предыдущий пример с некоторыми дополнительными функциями. Сделайте
ВЫБРАТЬ start_terminal,
duration_seconds,
SUM (duration_seconds) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal) AS running_total,
COUNT (duration_seconds) БОЛЕЕ
(РАЗДЕЛЕНИЕ ПО start_terminal) AS running_count,
AVG (duration_seconds) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal) AS running_avg
ИЗ tutorial.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
В качестве альтернативы те же функции с ЗАКАЗАТЬ ПО :
ВЫБРАТЬ start_terminal,
duration_seconds,
SUM (duration_seconds) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY start_time)
AS running_total,
COUNT (duration_seconds) БОЛЕЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY start_time)
AS running_count,
AVG (duration_seconds) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY start_time)
AS running_avg
ИЗ учебника.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
Убедитесь, что вы подключили эти два предыдущих запроса в Mode и выполняете их. Эта следующая практическая задача очень похожа на примеры, поэтому попробуйте изменить приведенный выше код, а не начинать с нуля.
Практическая задача
Напишите запрос, который показывает общую продолжительность поездок на велосипеде (аналогичный последнему примеру), но сгруппированный по end_terminal , а продолжительность поездки отсортирована в порядке убывания.
ЧИСЛО СТРОКИ ()
ROW_NUMBER () делает именно то, на что похоже - отображает номер данной строки. Он начинается с 1 и нумерует строки в соответствии с частью ORDER BY оператора окна. ROW_NUMBER () не требует указывать переменную в круглых скобках:
ВЫБРАТЬ start_terminal,
начальное время,
duration_seconds,
ROW_NUMBER () OVER (ЗАКАЗАТЬ ПО start_time)
AS row_number
ИЗ учебника.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
Использование предложения PARTITION BY позволит вам снова начать отсчет 1 в каждом разделе. Следующий запрос снова запускает счет для каждого терминала:
ВЫБРАТЬ start_terminal,
начальное время,
duration_seconds,
ROW_NUMBER () ВЫШЕ (РАЗДЕЛ НА start_terminal
ЗАКАЗАТЬ ПО start_time)
AS row_number
ИЗ tutorial.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
RANK () и DENSE_RANK ()
RANK () немного отличается от ROW_NUMBER () .Если вы заказываете до start_time , например, может случиться так, что на некоторых терминалах есть поездки с двумя одинаковыми временами начала. В этом случае им присваивается один и тот же ранг, тогда как ROW_NUMBER () дает им разные номера. В следующем запросе вы заметили 4-е и 5-е наблюдения для start_terminal 31000 - им обоим присвоен ранг 4, а следующий результат получает ранг 6:
ВЫБРАТЬ start_terminal,
duration_seconds,
RANK () OVER (РАЗДЕЛЕНИЕ ПО start_terminal
ЗАКАЗАТЬ ПО start_time)
Рейтинг AS
ИЗ учебника.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
Вы также можете использовать DENSE_RANK () вместо RANK () в зависимости от вашего приложения. Представьте себе ситуацию, в которой три записи имеют одинаковое значение. Используя любую команду, все они получат одинаковый ранг. Для этого примера предположим, что это «2». Вот как две команды будут по-разному оценивать следующие результаты:
-
RANK ()присвоит идентичным строкам ранг 2, затем пропустит ранги 3 и 4, поэтому следующий результат будет 5 -
DENSE_RANK ()все равно присвоит всем идентичным строкам ранг 2, но следующая строка будет иметь ранг 3 - никакие ранги не будут пропущены.
Практическая задача
Напишите запрос, который показывает 5 самых длинных поездок от каждого стартового терминала, упорядоченных по терминалам, и самые длинные поездки в пределах каждого терминала. Ограничены поездками, совершенными до 8 января 2012 года.
Попробуй это Посмотреть ответNTILE
Вы можете использовать оконные функции, чтобы определить, в какой процентиль (или квартиль, или любое другое подразделение) попадает данная строка. Синтаксис: NTILE (* # сегментов *) .В этом случае ORDER BY определяет, какой столбец использовать для определения квартилей (или любого количества плиток, которое вы укажете). Например:
ВЫБРАТЬ start_terminal,
duration_seconds,
NTILE (4) НАД
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
Квартиль AS,
NTILE (5) НАД
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
Квинтиль AS,
NTILE (100) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
Процентиль AS
ИЗ учебника.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
ЗАКАЗАТЬ ПО start_terminal, duration_seconds
Глядя на результаты вышеприведенного запроса, можно увидеть, что столбец процентиль рассчитывается не так, как вы могли ожидать. Если бы у вас было только две записи и вы измеряли процентили, можно было бы ожидать, что одна запись будет определять 1-й процентиль, а другая - 100-й процентиль. Используя функцию NTILE , вы фактически увидите одну запись в 1-м процентиле и одну во 2-м процентиле.Вы можете увидеть это в результатах для start_terminal 31000 - столбец процентиль выглядит как числовое ранжирование. Если вы прокрутите вниз до start_terminal 31007, вы увидите, что процентили вычисляются правильно, поскольку для этого start_terminal имеется более 100 записей. Если вы работаете с очень маленькими окнами, помните об этом и подумайте об использовании квартилей или аналогичных небольших полос.
Практическая задача
Напишите запрос, который показывает только продолжительность поездки и процентиль, в которую она попадает (по всему набору данных, а не по терминалам).
Попробуй это Посмотреть ответLAG и LEAD
Часто бывает полезно сравнить строки с предыдущими или последующими строками, особенно если у вас есть данные в разумном порядке. Вы можете использовать LAG или LEAD для создания столбцов, которые извлекают значения из других строк - все, что вам нужно сделать, это указать, из какого столбца извлекать и сколько строк вы хотите извлекать. LAG тянет из предыдущих рядов, а LEAD тянет из следующих рядов:
ВЫБРАТЬ start_terminal,
duration_seconds,
LAG (duration_seconds; 1) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds) AS отставание,
LEAD (duration_seconds; 1) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds) КАК ведущий
ИЗ учебника.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
ЗАКАЗАТЬ ПО start_terminal, duration_seconds
Это особенно полезно, если вы хотите вычислить разницу между строками:
ВЫБРАТЬ start_terminal,
duration_seconds,
duration_seconds -LAG (duration_seconds, 1) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
Как разница
ИЗ tutorial.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
ЗАКАЗАТЬ ПО start_terminal, duration_seconds
Первая строка столбца разницы пуста, потому что нет предыдущей строки, из которой можно было бы извлечь.Аналогично, использование LEAD создаст нули в конце набора данных. Если вы хотите сделать результаты немного чище, вы можете заключить его во внешний запрос, чтобы удалить нули:
ВЫБРАТЬ *
ИЗ (
ВЫБЕРИТЕ start_terminal,
duration_seconds,
duration_seconds -LAG (duration_seconds, 1) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
Как разница
ИЗ tutorial.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
ЗАКАЗАТЬ ПО start_terminal, duration_seconds
) sub
ГДЕ под.разница НЕ НУЛЬ
Определение псевдонима окна
Если вы планируете написать несколько оконных функций в одном запросе, используя одно и то же окно, вы можете создать псевдоним. Возьмите пример NTILE выше:
ВЫБРАТЬ start_terminal,
duration_seconds,
NTILE (4) НАД
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
Квартиль AS,
NTILE (5) НАД
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
Квинтиль AS,
NTILE (100) ВЫШЕ
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
Процентиль AS
ИЗ учебника.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
ЗАКАЗАТЬ ПО start_terminal, duration_seconds
Это можно переписать как:
ВЫБРАТЬ start_terminal,
duration_seconds,
NTILE (4) OVER ntile_window AS,
NTILE (5) OVER ntile_window AS, квинтиль,
NTILE (100) OVER ntile_window AS процентиль
ИЗ tutorial.dc_bikeshare_q1_2012
ГДЕ start_time <'2012-01-08'
ОКНО ntile_window AS
(РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
ЗАКАЗАТЬ ПО start_terminal, duration_seconds
Предложение WINDOW , если оно есть, всегда должно стоять после предложения WHERE .
Расширенные методы работы с окнами
Вы можете проверить полный список оконных функций в Postgres (синтаксис, используемый Mode) в документации Postgres. Если вы используете оконные функции в подключенной базе данных, вам следует изучить соответствующее руководство по синтаксису для вашей системы.
Введение в функции окна SQL
Очень мощная функция, которую вы любите ненавидеть (но нужно знать)
Оконные функцииSQL предоставляют чрезвычайно мощные и полезные функции.Но для многих, поскольку они настолько чужды стандартному SQL, их трудно изучить и понять, они имеют странный синтаксис - и их очень часто избегают.
Оконные функции можно просто объяснить как вычислительные функции, аналогичные агрегированию, но если обычное агрегирование с помощью предложения GROUP BY объединяет, а затем скрывает отдельные агрегированные строки, оконные функции имеют доступ к отдельным строкам и могут добавлять некоторые атрибуты из них. строк в набор результатов.
В этом руководстве по оконным функциям SQL я собираюсь помочь вам начать работу с оконными функциями, объяснить преимущества и время их использования, а также привести реальные примеры, помогающие разобраться в концепциях.
Окно в ваши данные
Одна из наиболее часто используемых и важных функций SQL - это возможность агрегировать или группировать строки данных определенным образом. Однако в некоторых случаях группировка может стать чрезвычайно сложной, в зависимости от того, что требуется.
Вы когда-нибудь хотели просмотреть результаты вашего запроса, чтобы получить рейтинг, список x лучших или что-то подобное? Были ли у вас какие-либо аналитические проекты, в которых вы хотели подготовить данные как раз для инструмента визуализации, но обнаружили, что это практически невозможно или настолько сложно, что оно того не стоит?
Оконные функции могут упростить задачу.После того, как вы получите результат своего запроса, то есть после предложения WHERE и любого стандартного агрегирования, оконные функции будут действовать на оставшиеся строки (окно данных ) и получить то, что вы хотите.
Некоторые из оконных функций, которые мы рассмотрим, включают:
-
БОЛЕЕ -
СЧЕТ () -
СУММ () -
НОМЕР СТРОКИ () -
РАНГ () -
DENSE_RANK () -
СВИНЦ () -
LAG ()
Более легкий
Предложение OVER определяет оконную функцию и всегда должно быть включено в оператор.По умолчанию в предложении OVER используется весь набор строк. В качестве примера рассмотрим таблицу сотрудников в базе данных компании и покажем общее количество сотрудников в каждой строке вместе с информацией о каждом сотруднике, в том числе о том, когда они начали работать в компании.
SELECT COUNT (*) OVER () As NumEmployees, firstname, lastname, date_started
ОТ Сотрудника
ЗАКАЗАТЬ ПО date_started;
| Количество сотрудников | имя | фамилия | date_started |
|---|---|---|---|
| 3 | Иоанна | Смит | 2019-01-01 00:00:00.000 |
| 3 | Салли | Джонс | 2019-02-15 00:00: 00.000 |
| 3 | Сэм | Гордон | 2019-02-18 00:00: 00.000 |
Вышеупомянутое, как и многие оконные функции, также может быть написано более привычным неоконным способом - что в этом простом примере неплохо:
ВЫБРАТЬ
(ВЫБЕРИТЕ COUNT (*) FROM Employee) как NumEmployees, firstname, lastname, date_started
ОТ Сотрудника
ЗАКАЗАТЬ ПО date_started;
А теперь, допустим, мы хотим показать количество сотрудников, которые начали в том же месяце, что и сотрудник в строке.Нам нужно будет сузить или ограничить счет только этим месяцем для каждой строки. Как это сделать? Мы используем предложение window PARTITION , например:
ВЫБРАТЬ СЧЕТЧИК (*) БОЛЕЕ (РАЗДЕЛЕНИЕ ПО МЕСЯЦУ (начало_даты), ГОДУ (начало_даты))
Как NumPerMonth,
DATENAME (месяц, date_started) + '' + DATENAME (год, date_started) как TheMonth,
имя Фамилия
ОТ Сотрудника
ЗАКАЗАТЬ ПО date_started;
| NumPerMonth | Месяц | Имя | Фамилия |
| 1 | января 2019 | Иоанна | Смит |
| 2 | февраль 2019 | Салли | Джонс |
| 2 | Февраль 2019 | Сэм | Гордон |
Разделы позволяют фильтровать окно на разделы по определенному значению или значениям.Каждый раздел окна часто называют окном , кадром .
Чтобы пойти дальше, допустим, мы не только хотели узнать, сколько сотрудников начали работу в том же месяце, но и хотим показать, в каком порядке они начали в этом месяце. Для этого мы можем использовать знакомое предложение ORDER BY . Однако внутри оконной функции ORDER BY действует немного иначе, чем в конце запроса.
ВЫБРАТЬ СЧЕТЧИК (*) БОЛЕЕ (РАЗДЕЛЕНИЕ ПО МЕСЯЦУ (начало_даты), ГОДУ (начало_даты))
ORDER BY date_started) As NumThisMonth,
DATENAME (месяц, date_started) + '' + DATENAME (год, date_started) как TheMonth,
firstname, lastname, date_started
ОТ Сотрудника
ЗАКАЗАТЬ ПО date_started;
| NumThisMonth | Месяц | Имя | фамилия |
| 1 | января 2019 | Иоанна | Смит |
| 1 | Февраль 2019 | Салли | Джонс |
| 2 | Февраль 2019 | Сэм | Гордон |
В этом случае ORDER BY изменяет окно таким образом, что оно идет от начала раздела (в данном случае месяца и года начала работы сотрудника) до текущей строки.Таким образом, счетчик перезапускается в каждом разделе.
Рейтинг
Оконные функции могут быть очень полезны для ранжирования. Ранее мы видели, что использование функции агрегирования COUNT позволило нам увидеть, в каком порядке сотрудники присоединяются к компании. Мы также могли бы использовать функции ранжирования окон, такие как ROW_NUMBER () , RANK () и DENSE_RANK () .
Различия можно увидеть после того, как мы добавим нового сотрудника в следующем месяце и удалим раздел:
ВЫБРАТЬ
ROW_NUMBER () БОЛЕЕ (ЗАКАЗ НА ГОД (дата_начала), МЕСЯЦ (начало_даты))
В качестве начального ранга
RANK () OVER (ORDER BY YEAR (date_started), MONTH (date_started)) как EmployeeRank,
DENSE_RANK () OVER (ORDER BY YEAR (date_started), MONTH (date_started)) как DenseRank,
DATENAME (месяц, date_started) + '' + DATENAME (год, date_started) как TheMonth,
firstname, lastname, date_started
ОТ Сотрудника
ЗАКАЗАТЬ ПО date_started;
| Начальный ранг | СотрудникРанг | ПлотныйРанг | Месяц | имя | фамилия | date_started |
| 1 | 1 | 1 | января 2019 | Иоанна | Смит | 2019-01-01 |
| 2 | 2 | 2 | Февраль 2019 | Салли | Джонс | 2019-02-15 |
| 3 | 2 | 2 | Февраль 2019 | Сэм | Гордон | 18.02.2019 |
| 4 | 4 | 3 | марта 2019 | Джули | Санчес | 2019-03-19 |
Вы можете увидеть различия. ROW_NUMBER () дает последовательный счет в пределах данного раздела (но при отсутствии раздела он проходит по всем строкам). RANK () дает ранг каждой строки на основе предложения ORDER BY . Он показывает ничьи, а затем пропускает следующий рейтинг. DENSE_RANK также показывает ничьи, но затем переходит к следующему последовательному значению, как если бы ничьей не было.
Другие функции ранжирования включают:
-
CUME_DIST- вычисляет относительный ранг текущей строки в разделе -
NTILE- Максимально равномерно делит строки для каждой оконной перегородки -
PERCENT_RANK- Процентный ранг текущей строки
Обратите внимание, что в этом примере вы можете использовать несколько оконных функций в одном запросе - и разделение, и порядок в каждом запросе могут быть разными!
Rows and Ranges and Frames, Oh My
Чтобы дополнительно определить или ограничить рамку окна в предложении OVER () , вы можете использовать ROWS и RANGE .С помощью предложения ROWS вы можете указать строки, включенные в ваш раздел, как строки, предшествующие или после текущей строки.
SELECT OrderYear, OrderMonth, TotalDue,
SUM (TotalDue) OVER (ORDER BY OrderYear, OrderMonth ROWS МЕЖДУ
НЕОГРАНИЧЕННЫЙ ПРЕДЫДУЩИЙ И 1 ПРЕДЫДУЩИЙ) КАК 'LaggingRunningTotal'
ОТ sales_products;
В этом примере рамка окна переходит от первой строки к текущей минус 1, и размер окна продолжает увеличиваться для каждой строки.
Range работает немного иначе, и мы можем получить другой результат.
SELECT OrderYear, OrderMonth, TotalDue,
SUM (TotalDue) OVER (ORDER BY OrderYear, OrderMonth RANGE МЕЖДУ
НЕОГРАНИЧЕННЫЙ ПРЕДЫДУЩИЙ И 1 ПРЕДЫДУЩИЙ) КАК 'LaggingRunningTotal'
ОТ sales_products;
Диапазон будет включать в себя те строки в рамке окна, которые имеют те же значения ORDER BY , что и текущая строка. Таким образом, возможно, что вы можете получить дубликаты с RANGE , если ORDER BY не уникален.
Некоторые описывают ROWS как физический оператор, а RANGE - логический оператор. В любом случае значения по умолчанию для ROWS и RANGE всегда равны UNBOUNDED PRECEDING AND CURRENT ROW .
Что еще?
Большинство стандартных агрегатных функций работают с оконными функциями. Мы уже видели COUNT в примерах. Другие включают SUM , AVG , MIN , MAX и т. Д.
С помощью оконных функций вы также можете получить доступ к предыдущим и последующим записям, используя LAG и LEAD , а также FIRST_VALUE и LAST_VALUE . Например, предположим, что вы хотите отобразить в каждой строке показатель продаж за текущий месяц и разницу между показателем продаж за последний месяц. Вы можете сделать что-то вроде этого:
ВЫБРАТЬ идентификатор, Месяц заказа, Год заказа, продукт, продажи,
sales - LAG (sales, 1) OVER (РАЗДЕЛЕНИЕ ПО ПРОДУКТУ ЗАКАЗ ПО ГОДУ ЗАКАЗА, МЕСЯЦ ЗАКАЗА) Как sales_change
ОТ sales_products
ГДЕ sale_year = 2019;
По сути, оконные функции SQL очень эффективны
Хотя это краткое введение в оконные функции SQL, надеюсь, оно пробудит у вас интерес, увидев все, на что они способны.Мы узнали, что оконные функции выполняют вычисления аналогично функциям агрегирования, но с дополнительным преимуществом, заключающимся в том, что они имеют доступ к данным в отдельных строках, что делает их весьма мощными. Они всегда содержат предложение OVER и могут содержать PARTITION BY , ORDER BY , а также множество агрегатных ( SUM , COUNT и т. Д.) И других позиционных функций ( LEAD , LAG. ). Мы также узнали о оконных фреймах и о том, как они инкапсулируют разделы данных.
Обратите внимание, что разные разновидности SQL могут реализовывать оконные функции по-разному, а некоторые могут не реализовывать все оконные функции или предложения. Обязательно ознакомьтесь с документацией для платформы, которую вы используете.
Если вы как разработчик SQL заинтересованы в настройке производительности своей базы данных SQL, ознакомьтесь с Настройка производительности базы данных SQL для разработчиков .
Счастливого окна!
Для получения дополнительной информации о конкретных реализациях см .:
Оконные функции SQL - необходимо знать инженерам по большим данным
Обзор
- Узнайте о функциях окна SQL
- Понять, чего не хватает агрегатным функциям и зачем нам нужны оконные функции в SQL
Введение
Объем данных растет с поразительной скоростью - до 44 зеттабайт в 2020 году! Само собой разумеется, что технология обработки таких гигантских объемов данных также меняется с соразмерной скоростью.
Сегодня у нас есть набор инструментов, таких как Hive и Spark, для обработки больших данных. Но, несмотря на то, что они различаются в некоторых аспектах, они по-прежнему используют основы SQL, что позволяет людям из всех слоев общества легко манипулировать большими данными. Хотя по некоторым аспектам SQL мы все еще нерешаемы. Итак, в этой статье я собираюсь поговорить об одном таком аспекте, в частности - оконных функциях.
Верно! В SQL есть такая вещь, как оконные функции, я не шучу! Судя по изумленному выражению вашего лица, эта статья кажется актуальной.До недавнего времени даже я не был слишком осведомлен об этих функциях, что говорит о том, насколько они недооценены.
Но дождитесь конца этой статьи, потому что оконные функции действительно поразят вас своей простотой, с которой они решают такие сложные проблемы. И да, инженеры по обработке данных, специалисты по обработке данных, аналитики данных и все остальные, кто заигрывает с данными, должны отдать должное этим функциям!
Прежде чем двигаться дальше, я предлагаю вам освоиться с основными функциями SQL, прочитав эту статью - 24 часто используемых функции SQL.А если вы заинтересованы в изучении SQL в формате курса, обратитесь к нашему курсу - Язык структурированных запросов (SQL) для науки о данных.
Содержание
- Представляем набор данных
- Где отстают агрегатные функции?
- Что такое оконные функции в SQL?
- Общие сведения о функциях окна SQL - пункт
- Окно с перегородкой BY
- Организация строк в разделах
- Оконные функции
- Номер строки
- Рейтинг vs Dense_Rank
- Nth_Value
- Нетто
- Опережение и отставание
- Файл кода SQL
Знакомство с набором данных
Прежде чем продолжить, позвольте мне познакомить вас с фиктивным набором данных, над которым мы будем работать в этой статье.Предположим, есть компания, которая ведет учет имени, должности и заработной платы сотрудника следующим образом:
Мы собираемся использовать этот образец набора данных, чтобы понять концепции, изложенные в этой статье. Хорошо, приступим!
Где отстают агрегатные функции?
Предположим, вы хотите определить общую заработную плату всех сотрудников компании. Как бы вы это сделали? Вы можете просто использовать агрегатную функцию SUM () в столбце SALARY.
Легко.
Как насчет определения общей заработной платы сотрудников по каждой категории должностей? Используйте последний запрос и добавьте предложение GROUP BY в столбец JOB.
Отлично!
Теперь позвольте мне задать вам еще два вопроса:
- Показать общую зарплату и общую зарплату для каждой категории должностей вместе со значением каждой строки.
- Расположите заработную плату в порядке убывания для каждой категории должностей.
Поняли? Нет? Наверное?
Это определенно было не так просто, как первые, которые вы могли получить мгновенно.Но почему?
Ну, если вдуматься, предыдущие запросы требовали простых агрегатных функций для решения проблемы. Агрегатные функции SQL дают нам только одно значение для группы строк, агрегированных вместе (подумайте о первом написанном нами запросе).
Но последние запросы нельзя было просто решить с помощью таких функций. Эти запросы хотят, чтобы мы сохранили исходную идентичность отдельных строк, что не удается решить агрегатным функциям. Поэтому для решения таких запросов нам нужны разные виды функций - оконные функции.
Что такое оконные функции в SQL?
Оконные функции выполняют вычисления для набора связанных между собой строк. Но, в отличие от агрегатных функций, оконные функции не сворачивают результат строк в одно значение. Вместо этого все строки сохраняют свою исходную идентичность, и вычисленный результат возвращается для каждой строки.
Общие сведения о функциях окна SQL - пункт
Например, если бы я отобразил общую зарплату сотрудников вместе со значением каждой строки, это выглядело бы примерно так:
Предложение OVER обозначает окно строк, к которому применяется оконная функция.Его можно использовать с агрегатными функциями, как мы использовали здесь с функцией SUM, тем самым превратив ее в оконную функцию. Или его также можно использовать с неагрегатными функциями, которые используются только как оконные (мы узнаем о них больше в следующих разделах).
Итак, синтаксис для определения простой оконной функции, которая выводит одно и то же значение для всех строк, выглядит следующим образом:
имя_функции окна (<выражение>) OVER ()
Но как насчет применения оконной функции к определенным строкам, а не ко всей таблице?
Окно с перегородкой BY
Предложение PARTITION BY используется вместе с предложением OVER.Он разбивает строки на разные разделы. Эти разделы затем обрабатываются оконной функцией.
Например, чтобы отобразить общую зарплату по каждой категории вакансий для всех строк, нам придется изменить наш исходный запрос SQL следующим образом:
Как видите, столбец total_job_salary отображает сумму продаж для этой конкретной категории вакансий, а не для всей таблицы.
Итак, синтаксис определения оконной функции для разделения строк следующий:
имя_функции окна (<выражение>) ВЫШЕ (<раздел_по_ предложению>)
А теперь, как насчет расположения строк в каждом разделе?
Расположение строк в разделах
Мы знаем, что для упорядочивания строк в таблице мы можем использовать предложение ORDER BY.Итак, чтобы расположить строки внутри каждого раздела, мы должны изменить предложение OVER с помощью предложения ORDER BY.
Здесь строки были разделены в соответствии с их категорией заданий, как указано в столбце JOB. При прокрутке вниз вы заметите, что столбец SALARY упорядочен в порядке убывания, а столбец Order_job_salary отображает промежуточную сумму категории задания (начиная с каждого раздела).
Итак, синтаксис для определения оконной функции для разделения строк и их упорядочения выглядит следующим образом:
имя_функции окна (<выражение>) ВЫШЕ (<раздел_по_ предложению> <предложение_порядка>)
Оконные функции
Теперь, когда мы знаем, как определять оконные функции с помощью предложения OVER и некоторых его модифицированных версий, мы наконец можем перейти к работе с оконными функциями!
1.Номер строки
Иногда в вашем наборе данных может не быть столбца, показывающего последовательный порядок строк, как в случае с нашим набором данных. В этом случае мы можем использовать оконную функцию ROW_NUMBER () . Каждой строке таблицы присваивается уникальный порядковый номер.
Обратите внимание, что нумерация начинается с 1. Кроме того, чтобы предотвратить любые конфликты с ключевым словом MySQL для функции, я заключил имя столбца в кавычки.
Но, поскольку это оконная функция, мы также можем ограничить ее разделами, а затем упорядочить эти разделы.
Здесь мы разделили строки в столбце JOB и упорядочили их на основе ЗАРПЛАТЫ сотрудника. Обратите внимание, как нумерация перезапускается каждый раз, когда начинается новый раздел.
Но предположим, что мы хотим ранжировать сотрудников по их зарплате?
2. Рейтинг по сравнению с Dense_Rank
Оконная функция RANK () , как следует из названия, ранжирует строки в их разделе на основе заданного условия.
Обратите внимание на выделенную часть.В случае ROW_NUMBER () у нас есть порядковый номер. С другой стороны, в случае RANK () у нас одинаковый ранг для строк с одинаковым значением.
Но здесь есть проблема. Хотя строкам с одинаковым значением присваивается один и тот же ранг, последующий ранг пропускает недостающий ранг. Это не дало бы желаемых результатов, если бы нам пришлось возвращать из таблицы «N первых различных» значений. Поэтому у нас есть другая функция для решения этой проблемы.
Функция DENSE_RANK () аналогична RANK () за исключением одного отличия: она не пропускает никакие ранги при ранжировании строк.
Здесь все ранги различны и последовательно увеличиваются в пределах каждого раздела. По сравнению с функцией RANK () она не пропускает ни одного ранга в разделе.
3. Nth_Value
Если вы хотите получить n-е значение из оконной рамки для выражения, вы можете использовать оконную функцию NTH_VALUE (выражение, N).
Например, чтобы получить третью по величине зарплату в каждой категории JOB, мы можем разделить строки в соответствии со столбцом JOB, затем упорядочить строки в разделах в соответствии с уменьшающейся зарплатой и, наконец, использовать функцию NTH_VALUE для получения значения .Команда будет такой:
Вы, должно быть, заметили что-то другое после предложения Order By. Это пункт Frame. Он определяет подмножество раздела (или фрейма), которое будет использоваться оконной функцией для вычисления значения для текущей строки.
Здесь я упомянул, что все предыдущие и последующие строки для текущей строки рассматриваются как внутри кадра при применении оконной функции. Но почему я использовал здесь предложение frame, а не с другими функциями? Это связано с тем, что другие оконные функции работают со всем разделом, даже если предоставляется предложение frame.Но только NTH_VALUE () может работать с кадрами внутри раздела.
Теперь предположим, что вы хотите вывести первое значение из каждого раздела? Хотя есть и функция FIRST_VALUE (), я собираюсь использовать для нее NTH_VALUE.
Точно так же у нас есть функция LAST_VALUE (). Но я собираюсь определить последнее значение в каждом разделе, как указано выше, хотя и в порядке убывания строк.
4. Ntile
Иногда может потребоваться отсортировать строки в разделе на определенное количество групп.Это полезно, когда вы хотите определить процентиль, квартиль и т. Д., В которые попадает конкретная строка. Для этих целей используется функция NTILE () . Он возвращает номер группы для каждой строки в разделе.
Например, давайте найдем квартиль для каждой строки на основе ЗАРПЛАТЫ сотрудника:
Аналогичным образом вы можете разделить строки на разное количество групп и вычислить NTILE для разных разделов.
5.Опережение и отставание
Часто бывает нужно сравнить значение текущей строки со значением предыдущей или последующей строки. Это помогает в легком анализе данных. Оконные функции LEAD () и LAG () предназначены только для этой цели.
Здесь мы создали новый столбец, содержащий SALARY из следующей строки в каждом разделе, упорядоченном по зарплате с помощью функции LEAD. Обратите внимание, что последняя строка каждого раздела содержит нулевое значение, поскольку нет следующей строки, из которой можно было бы извлечь данные.
Теперь сделаем то же самое с функцией LAG.
Здесь мы создали два новых столбца. Первый столбец содержит ЗАРПЛАТУ из предыдущей строки в каждом разделе, отсортированном по зарплате. В то время как второй столбец содержит разницу между SALARY из предыдущей строки и текущей строки. Как видите, это очень полезно для быстрого анализа разницы между зарплатами в одном разделе.
Файл кода SQL
Весь код SQL, связанный с оконными функциями для этой статьи, можно найти по этой ссылке.
Примечания
К настоящему времени мы видели довольно много оконных функций, и я надеюсь, что теперь вы можете оценить красоту оконных функций в SQL. Но обучение на этом не заканчивается. Освоив основы, пора овладеть инструментами для работы с большими данными.
Если вы хотите перейти в область Data Engineering, я предлагаю следующие статьи для легкого перехода -
.