Примитивный запрос — простой джойн и группировка. Традиционные методы оптимизации — казалось бы, что могло пойти не так?..
Небольшой эксперимент, на тему необходимости проверки любых гипотез в конкретных условиях.
Возьмем исходный запрос:
WITH vals AS ( SELECT i , unnest('{1,2,3,4,5,6,7,8}'::integer[]) v FROM generate_series(1, 10000) i ) SELECT v2.i , sum(v1.v) FROM vals v1 JOIN vals v2 USING(i) GROUP BY 1;
294ms — это будет наше стартовое время, которое мы попробуем ускорить. Ну и 640K записей, которые пришлось обработать в Merge Join.
Внимание на ключи группировки!
У нас в запросе используется USING(i) — то есть ON v1.i = v2.i, а потом — GROUP BY 1 — группировка по первому полю результата, которым в нашем случае является v2.i.
То есть происходит группировка по полю связанной таблицы, а сама агрегация — по данным основной таблицы! Не надо так. Этим вы отсекаете планировщику возможность рассмотреть вариант соединения таблиц уже после группировки.
Исправим эту досадную помарку. Но в нашем примере для CTE это не влияет.
Зачем нам соединение таблиц?
Раз мы выяснили, что вся группировка может быть получена уже по первой таблице, то [INNER] JOIN можно заменить проверкой наличия такого значения в таблице «справа»:
WITH vals AS (...) SELECT i , sum(v) FROM vals WHERE i IN (SELECT DISTINCT i FROM vals) GROUP BY 1;
Всего 85ms и Merge Join заменился на Hash Join, выдающий всего 80K записей.
Не все массивы одинаково полезны
Буквально, «на автомате» исправляем IN (...) на = ANY(ARRAY(...)), ведь это эффективно предотвращает возможное «разворачивание» в соединение обычного сканирования таблицы с константным условием:
WITH vals AS (...) SELECT i , sum(v) FROM vals WHERE i = ANY(ARRAY(SELECT DISTINCT i FROM vals)) GROUP BY 1;
И… грабли больно бьют нас по лбу: 2609ms — почти в 10 раз хуже первоначального времени! А все потому, что проверить 80K раз наличие элемента в массиве на 10K элементов — ни разу не дешево, и такую технику оптимизации можно использовать только при достаточно «коротких» массивах.
GROUP(JOIN) vs JOIN(GROUP)
Но у нас по-прежнему условия соединения проверяются для 80K записей, а «на выход» отдается всего 10K — как бы их сократить?.. Для этого внесем группировку «под скобки»:
WITH vals AS (...) SELECT * FROM ( SELECT i , sum(v) FROM vals GROUP BY 1 ) grp WHERE i IN (SELECT DISTINCT i FROM vals);
Итого: 68ms, или в 4.5 раз быстрее оригинала.
Понятно, что если мы обратим внимание на сам источник данных, то и множественные обращения к нему можно было бы сократить.
К настоящему моменту накопилось досточно данных для выполнения заявленного анализа. Будут использованы данные за октябрь, ноябрь и декабрь 2021 года, поскольку эти месяцы как содержат российский эпид. сезон, так и являются однородными с точки зрения штамма вируса: в обеих странах в этот период был распространен Дельта- вариант. Данные для анализа взяты из официальных источников России и США.
Смертность и вакцинный статус
Во времена Дельта- штамма основной причиной избыточной смертности называли непривитое население: дескать, именно они заболевают Covid-19 и впоследствии умирают. Определим избыточную смертность как отношение умерших за месяц 2021 года к одноименному месяцу 2020 года. В таком случае если построить график избыточной смертности от доли непривитого населения, то мы должны получить растущую прямую, выходящую из точки (0, 0) и достигающую значения (1, 1).
Действительно, если все население привито, то избыточная смертность должна находиться в районе 0 (поскольку в таком случае практически никто не умирает), а при полностью непривитом населении смертность должна повторить значение предыдущего года. В этом разделе публикации мы построим эти графики для обеих стран и сравним с ранее выдвинутым теоретическим утверждением.
Может показаться, что задача очень проста: необходимо усредняя по стране для каждого месяца определить среднюю избыточную смертность (координата х) + долю невакцинированного населения (координата y) и нанести эти точки на график. Но в этом случае у нас будет неявная зависимость от времени, поскольку разные точки одного графика будут относиться к разным месяцам. А в разные месяцы большое количество факторов (погода, питание, спортивная активность, достаток, традиции, принятые ограничительные меры….) могут иметь разное значение, поэтому мы получим зависимость избыточной смертности от вакцинации и большого количества факторов, косвенно участвующих в виде времени.
Отдельно выделю фактор стадии эпид. процесса, который задает различное положение максимумов смертности в разных годах. Это приводит к тому, что есть как месяцы с завышенной избыточной смертностью (где был максимум в 2021 году), так и с заниженной (где был максимум в 2020 году). Например, для г.Москва максимальные значения заболеваемости отличаются от фоновых примерно в 3 раза, поэтому избыточная смертность будут колебаться в 9 раз! На фоне подобных «качелей» избыточной смертности оценки эффективности вакцинации будут содержать большие ошибки.
Казалось бы из- за действия сильных зашумляющих факторов задача превращается в нерешаемую. Но для подавления шумов мы задействуем тот же прием, который используется при анализе сильно зашумленного космического излучения.
Представьте, что у вас есть небольшой полезный сигнал в виде периодического маленького «тычка», на фоне 100- кратно превышающего его белого шума. Выдилеть полезный сигнал из этой совокупности практически нереально. Но если мы 100 раз сложим этот зашумленный сигнал сам с собой, то полезная составляющая усилится в 100 раз (поскольку «тычок» имеет постоянное значение), а шум ослабнет (поскольку он случаен, т.е. разнонаправлен и будет сам себя подавлять). Так можно получить полезный сигнал из сильно зашумленных данных.
Поэтому мы нанесем каждый регион на график отдельной точкой и несмотря на то, что на регионы действует большое количество факторов, мы в силу их рассогласованности при усреднении получим сильно ослабленное шумовое воздействие. Поэтому точки, представляющие отдельные регионы, не имеют самостоятельного смысла, а результатом анализа можно считать только наклон асимптотической прямой (отражающей усредненное значение).
Россия
В нашей стране есть два независимых источника данных по смертности от Covid- 19: сайт стопкоронавирус и официальные отчеты Росстата. Первый дает сильно заниженные данные как по заболеваемости, так и по умершим, поэтому летальность в различных регионах из- за подобных корректировок отличается в разы. Я считаю данные сайта стопкоронавирус ненадежными и буду использовать помесячные данные Росстата. При анализе я не учитывал регионы со смертность в 2020 году ниже 1000 человек / месяц, поскольку в этом случае случайные отклонения могут ощутимо исказить статистику.
Для получения данных по вакцинации использовался сайт gogov.ru, который детализирует данные по регионам и датам. На этом сайте представлено два числовых показателя: вакцинированно и полностью вакцинированно. При построении отчетов я приводил оба значения, но рекомендую опираться на первые, поскольку их больше и как следствие статистика получается более точная.
При соединении наборов данных получены графики, каждая точка на которых представляет отдельный регион. Данные по вакцинации брались за 5 дней до начала соотвествующего месяца. Это учитывало как временной лаг для протекания болезни (-10 дней), так и отставание опубликованных данных от реального положения в регионе (+5 дней).
Голубая область задает границы 95% уверенности нахождения апроксимирующей прямой. Уравнения приведены для искомой апроксимации.
По графикам видно, что для России ранее выдвинутое теоретическое утверждение (о том, что от Covid- 19 умирает в основном непривитое население) не соответствует действительности: в октябре и ноябре в популяции наличиствует обратная зависимость (чем больше в регионе непривитого населения, тем в нем ниже избыточная смертность), а декабрь показывает недостаточно сильную зависимость (мы ожидаем коэффициент наклона близкий к значению 1).
США
Данные по вакцинации взяты с сайта Центра по контролю и профилактике заболеваний США (его роль заключается в предоставлении информации для улучшения решений в области здравоохранения). На том же сайте в другом наборе данных представлена информация о смертности от всех причин.
Дополнительным бонусом данных из США оказалось разделение населения по возрастным группам как среди вакцинированных, так и среди умерших, что позволяет более детально проаналазировать воздействие вакцинации. Позже анализируя эту разницу мы получим интересные выводы.
Графики, построенные по тем же правилам, что и для России.
По США ранее выдвинутое теоретическое утвеждение также не подтвержается: асимптотическая прямая имеет обратную зависимость (т.е. чем больше в регионе непривитого населения, тем в нем ниже избыточная смертность).
Эффективность вакцинации
А теперь наконец перейдем к основной теме публикации. Мы уже косвенно оценили, что эффективность вакцинации далека от 1, поскольку для обеих стран не выполнилась исходная теория. Но все же хочется иметь какие- то более точные оценки.
Первый способ
Одну из оценок можно сделать на основании того, что рассматриваемые месяцы идут непрерывно. В этом случае если и произойдет сдвижка максимума смертности одного года относительно другого, то это в себе учтет следующий месяц. Т.е. эффективность вакцинации можно оценить как максимум разницы между 1 и средней избыточной смертности. Обращаясь к графикам США для тотально вакцинированной группы 65+, мы видим, что минимальное отклонение центра красной линии от 1 равно 0.2. Это и есть первая оценка эффективности вакцинации.
Второй способ
Ранее я говорил о большом количестве факторов, действующих в регионе. Но для отдельно взятого месяца это влияние должно быть одинаковым на обе возрастные группы (65+ и 65-). Таким образом, анализируя отличия избыточной смертности и долей для центра краной линии, можно заключить, что эффективность вакцинации в ноябре составляла примерно 0.3, а в декабре примерно 0.45. Отличия по двум месяцам скорее всего объясняются значительно долей популяции 65+, поставивших в декабре бустерную дозу вакцины (у которой пока еще сильное воздействие на организм).
Так как по США мы имеем более правдоподобные данные заболеваемости, то можно учесть то, что за промежуточный год по официальным данным переболело ~10% населения (на практике я думаю это значение примерно в 2-3 раза выше), часть из которого не пережило заболевание. Так популяция США «приспособилась» к новому вирусу и потому в 2021 году должна иметь на 10% меньшую смертность, чем в 2020.
Вывод: эффективность вакцинации от Дельта- штамма в России и США лежит в интервале от 15% до 30%.
Продолжая тему нарезки картинок при помощи бессерверных решений, хочу предложить вашему вниманию дополнение в виде бессерверного frontend и backend.
AWS S3 предлагает возможность загружать картинки напрямую в S3 из приложения. Архитектурно это выглядит вот так:
Я же предлагаю реализовать Frontend, Backend, а также нарезку картинок, описанную в этой статье более подробно, при помощи бессерверное решений. Итак, полная архитектура будет выглядеть вот так:
Для реализации этого для начала напишем простой backend, который будет создавать подписанную ссылку на загрузку картинок:
Уровень бесплатного использования API Gateway включает один миллион вызовов API HTTP, один миллион вызовов API REST, один миллион сообщений и 750 000 минут подключения в месяц на протяжении до 12 месяцев.
Новые клиенты AWS ежемесячно получают 5 ГБ хранилища Amazon S3 класса S3 Standard, 20 000 запросов GET, 2000 запросов PUT, COPY, POST, или LIST, а также 100 ГБ исходящего трафика.
В процессе разработки приложения, поскольку у нас не было четкого технического задания и видения, у нас возник некий технический долг в виде any, в этой статье мы постараемся это исправить. Для тех кто пропустил, начало. И в этом нам поможет ESLint — утилита, которая может анализировать написанный код, она может находить синтаксические ошибки, баги или неточности форматирования.
По умолчанию версия Angular 12 не включает линтеры, потому что стандартный линтер TypeScript TSLint считается устаревшим и вместо него рекомендуется использовать ESLint.
Для начала использования:
Добавляем правила, для этого создаем новый файл «.eslintrc» в корневой папке со следующими значениями:
Где rules — набор eslint правил, parser — парсер, который будет валидировать содержимое файлов, plugins — набор подключаемых плагинов, которые не входят в стандартный набор eslint, extends — набор предустановленных рекомендаций, которые будут применяться.
Нам достаточно будет установленых по умолчанию правил, посмотреть какие правила сюда входят можно здесь, добавить свои правила можно здесь.
Запускаем командой npm run lint и смотрим на результат.
очень много ошибок
Результат как видим не утешительный ✖ 42 problems (17 errors, 25 warnings)
Чтобы не выбирать ошибки из консоли, для тех кто использует VS Code можно установить ESLint от Microsoft. ESLint подсветит критические ошибки красным, а предупреждения желтым. При наведении мы увидим подсказку, что конкретно нужно исправить.
Вот так выглядит подсветка ошибок в VS Code с плагином ESLint от Microsoft
Как видим ошибок очень много, но в основном они однотипные.
Например, ‘cX’ is never reassigned. Use ‘const’ instead — говорит о том, что нужно использовать const, а не let. Свойство «data» объявлено, но его значение не было прочитано, так гласит — ‘data’ is assigned a value but never used.
Кроме этого у нас есть неиспользуемые свойства и методы, например ngOnInit, constructor и т.д. Но самые страшные ошибки это конечно же нарушение использования типов. Например мы используем как пустой объект {}, что является ошибкой и нам подсказывают: Если вам нужен тип, означающий «любой объект», вам, нужно использовать Record<string, unknown>; вместо {}. Если вам нужен тип, означающий «пустой объект», вы можете использовать Record<string, never>; вместо {}.
На самом деле нам известны типы данных которые мы используем поэтому мы внесем изменения в наш canvas-post.interface.ts и добавим парочку интерфейсов.
За фиктивным контрактом на разработку игры скрывалось полноценное дизайнерское исследование. Именно оно положило начало целой индустрии.
В 1972 году миллионы людей в США и во всем мире познакомились с одной знаковой видеоигрой. Это был Pong. Его история рассказывалась публике уже десятки раз, и, конечно же, с каждым пересказом она становилась все более захватывающей. Бушнелл и Алкорн, так же как Джобс и Возняк, стали культурными идолами эпохи. К сожалению, их слишком часто изображают в виде безумных ученых, гениев, которые проснулись однажды утром, воскликнули «Эврика!» и принялись за создание первых в мире видеоигр и персональных компьютеров соответственно.
С чего все началось
Однако Pong не был первой видеоигрой с монетоприемником, не говоря уже о первенстве на рынке «игр на телевизоре». Скажем больше: компьютерные игры с мячиком и ракетками тоже не были в новинку для ранних компьютерных геймеров. Как вышло, что такая примитивная штука как Pong вообще завоевала сердца миллионов людей по всему свету?
Наверное, здесь стоит сделать короткое отступление и сказать пару слов о компьютерной истории.
Отправные точки отрасли компьютерных вычислений можно отыскать в технологиях Второй мировой войны. Армия США постоянно стремилась одержать техническое первенство, и несколько перспективных проектов — да и не очень перспективных тоже — получили финансирование в расчете на успех. Одним из таких проектов было создание высокоскоростного электронного устройства для расчета баллистических таблиц стрельбы, который в то время выполнялся вручную, преимущественно женщинами-математиками.
Разработка «электронного числового интегратора и компьютера», более известного под сокращенным именем ENIAC, началась еще в 1943-м, однако в полную силу он заработал только к 1946 году, став первым комплексным перепрограммируемым цифровым вычислительным устройством.
Задуманный и спроектированный Джоном Мокли и Джоном Экертом ENIAC размером с комнату повлиял на разработку все более компактных и мощных коммерческих компьютеров.
Эти события по-своему предвосхитили изобретение видеоигр. Военные разработки понемногу перекочевали в мир гражданской электроники. Ненадежные газоразрядные лампы понемногу вытеснились чипами. Дорогие компоненты стали стремительно дешеветь. Всё это подготовило почву для появления на рынке огромного количества видеоигр.
Та самая маленькая ложь
Непосредственно Pong разработали два инженера-электротехника — Нолан Бушнелл и Аллан Алкорн. Вместе с Тедом Дабни они основали компанию Atari Inc. в Саннивейле, штат Калифорния. Однажды Бушнелл, желая простимулировать Алкорна, сказал ему, что Atari якобы получила контракт от General Electric Co. на разработку некоего потребительского игрового продукта, и поручил построить новую игровую машину, пинг-понг с мячом, двумя ракетками и счетом, в которую можно было бы играть на телевизоре.
Аллан Алкорн, anonradio.netНолан Бушнелл в своей домашней мастерской, Photo: Roger Ressmeyer/Corbis/VCG Тед Дабни, gamemag.ru
«Не было никакого большого контракта», — признался позже Алкорн. «Нолан просто хотел дополнительно мотивировать меня на работу, чтобы я сделал что-то качественное и интересное. Это было похоже на упражнение по дизайн-мышлению: он выдумал как нельзя более простую игру, чтобы побудить меня разобраться с новыми технологиями».
Ключевым элементом системы, с которым Алкорну довелось экспериментировать, по его словам, была новая электронная схема перемещения объектов. Бушнелл разработал ее годом ранее, когда он работал в Nutting Associates. Он впервые использовал эту схему в аркадной игре под названием Computer Space. Игра была продана немалым тиражом в 2000 экземпляров, но так и не стала хитом.
Computer Space, videogamehistorian.wordpress.com
Интерес Бушнелла к компьютерным играм возник еще в 1960-х, когда он был студентом.
Параллельно с учебой Бушнелл работал в парке аттракционов, а после колледжа любил поиграть в космические игры на компьютере PDP-10. Приблизительно тогда же к нему пришла идея построить собственный игровой автомат на базе PDP. Он узнал стоимость компьютера и разделил ее на сумму, которую зарабатывала средняя аркадная игра — и сразу же отказался от такой коммерческой затеи, потому что ее экономика трещала по швам. Машина ни при каком раскладе не могла окупиться.
А в 1971 году Бушнелл впервые увидел компьютер Data General, выставленный на продажу по цене 5000 долларов, и, произведя новые расчеты, понял, что игра на шести терминалах, подключенных к этому компьютеру, может оказаться прибыльной. Он начал разрабатывать космическую стрелялку для системы с разделением временем. Однако дело не обошлось малой кровью. Поскольку все игровые события нужно было просчитывать здесь и сейчас, компьютер перегружался и страшно тормозил.. Бушнелл попытался снять нагрузку с центрального компьютера, сделав терминалы более «умными», добавив в каждый из них генератор синхронизации, затем схемы для отображения звездного поля… и так до тех пор, пока компьютеру не осталась одна единственная задача: следить за тем, где находятся игроки. По воспоминаниям Бушнелла, в этот момент он понял, что центральный компьютер вообще не нужен — терминалы могут работать самостоятельно. Это озарение, с одной стороны, позволило отказаться от закупки компьютеров, а с другой кратно удешевляло производство игровых машин.
«Вообще-то заказ на покупку компьютеров был уже оформлен, но жена Бушнелла по рассеянности забыла отнести его на почту» — вспоминает Алькорн. И тут же добавляет: «Мы бы точно разорились, будь она чуть повнимательнее!».
Бушнелл пишет: «Экономическая модель проекта перестала считаться как «компьютер за $6000, плюс куча оборудования». Теперь нам хватало терминальных комплектующих за $400 долларов, монитора за $100 и машинного шкафа еще за $100. Цифры сошлись, и лед буквально оттаял в моих жилах!».
Разработанная Бушнеллом игра Computer Space понравилась только искушенным игрокам — тем, кто был ранее знаком с космическими играми на мейнфреймах, или тем «любителям», которые и по сей день часто посещают залы игровых автоматов. Она опередила свое время. А Pong, напротив, был слишком прост, чтобы такой интеллектуал, как мистер Бушнелл, мог рассматривать его как полноценную игру — и именно поэтому он имел успех.
Схема Бушнелла, предназначенная для управления перемещением объектов в Computer Space, оказалась слишком сложна для Алкорна. Он предпочел не тратить время на ее доскональное изучение и переработал ключевые узлы с нуля. Кроме того, перед ним стояла задача как можно сильнее снизить конечную стоимость комплектующих, и это потребовало большой изобретательности и некоторых компромиссов.
«В 1972 году на рынке еще толком не было дешевой оперативной памяти», — говорил он. «Мы столкнулись с необходимостью обеспечить движение шарика в любое из мест массива 200 на 200 точек без возможности сохранить это движение. Задачу удалось решить при помощи 10 готовых TTL-деталей. Мы построили особые генераторы синхронизации…».
Таким образом, шарик двигался относительно экрана как по вертикали, так и по горизонтали, наподобие кадровых «полос» на неправильно настроенном телевизоре. Алкорн вспоминает, что первоначально он использовал микросхему от Fairchild для создания дисплея со счетом, но она стоила 5 долларов, а он мог сделать то же самое за 3 доллара, используя TTL-детали, хотя подсчет очков получался более грубым. Собственно, на последнем варианте он и остановился.
Atari Pong, Wikimedia Commons
Возможно, вы никогда не обращали на это внимания, но мячик в Pong квадратный — это еще один компромисс. Учитывая, сколько схем потребовалось бы для круглого мяча, Алкорн задал коллегам риторический вопрос: «Кто будет платить лишний четвертак за такую мелочь как круглый мяч?».
Звуки, издаваемые игрой, также стали для основателей Atari предметом разногласий. Бушнелл хотел получить одобрительный рев многотысячной толпы, Дабни же был согласен на «освистывание».
«Как извлечь звуки из дешевых цифровых компонентов?» — задумался Алкорн. «Сначала я погорячился, сказал, что у меня в распоряжении нет подходящих деталей. Затем пошарил внутри генератора вертикальной синхронизации в поисках подходящих частот и в результате сделал самый дешевый генератор звука из возможных».
Разработка аппаратной части Pong заняла почти три месяца. Готовый прототип Алкорна включал 73 микросхемы, что, при цене около 50 центов за штуку, составляло 30-40 долларов США. «Слишком далеко от цены потребительского продукта. Плюс еще и упаковка! Я расстроился, однако Нолан моего пессимизма не разделял. Он сказал тогда: «Выглядит очень неплохо!».
Первый прототип Pong Бушнелл и Нолан установили в местном баре. На следующий же день владелец помещения позвонил им и попросил забрать. Дескать, машина чуть-чуть поработала и тут же сломалась. По прибытии проблема вскрылась моментально: монетоприемник был до отказа забит четвертаками.
Уже потом, спустя почти 10 лет, компания Atari превратилась в мирового гиганта-производителя 8-битных (а затем и 16-битных) компьютеров и консолей. Не все решения компании приводили к успеху, сравнимому с первым детищем, Pong. Как вы все знаете, принцип «делай просто, делай быстро» стал одной из причин появления на свет игры E.T., обрушившей мировой рынок видеоразвлечений. Но это уже совсем другая история.
