Мультипарадигменний SQL-запит

Я не є великим прихильником "потужної" бізнес-логіки в збережених SQL-процедурах, в першу чергу через складність її покривання юніт-тестами. Але ліди на моєму поточному проєкті дотримуються іншої думки, вважаючи, що що менше викликів API робитиме до SQL-сервера, то більшою буде загальна продуктивність системи, і переваги цього факту, як на них, такі великі, шо в певних випадках тестове покривання можна логіки принести в жертву.

Одного дня до мене надійшла доволі цікава задача, для якої я написав найдовший в своїй девелоперській кар'єрі SQL-запит. Контекст задачі:

• вебзастосунок обліковує сутності, звані "stakeholder";
• ці стейкхолдери можуть бути двох типів — особа чи організація;
• стейкхолдери можуть бути пов'язані різними зв'язками, серед яких є зв'язки:
  • особи до організації як "робітник-роботодавець";
  • організації до особи як "роботодавець-робітник";
• кожен зі стейкхолдерів має, окрім інших, властивість "sentiment";
• ці сентименти відображають колір - ЗЕЛЕНИЙ, ПОМЕРАНЧЕВИЙ, ЧЕРВОНИЙ або NOT ENGAGED; колір визначається на підставі бізнес-параметрів;
• значення стейтментів зберігаються як char, відповідно 'G', 'A', 'R', 'N';
• стейкхолдери-особи отримують свій сентімент напряму від користувача застосунку;
• а от стейкхолдери-організації свій сентімент мають розраховувати відносно пропорційної кількості сентиментів пов'язаних стейкхолдер-осіб, що не мають сентімент NOT ENGAGED:
  • якщо 10% або більше стейкхолдер-осіб мають сентімент ЧЕРВОНИЙ, то стейкхолдер-організація також матиме сентімент ЧЕРВОНИЙ;
  • інакше якщо 80% або більше осіб мають ЗЕЛЕНИЙ, то організація матиме також ЗЕЛЕНИЙ;
  • інакше якщо є хоч одна особа з сентіментом ПОМЕРАНЧЕВИЙ, пов'язана організація матиме також ПОМЕРАНЧЕВИЙ;
  • інакше NOT ENGAGED.

На мій перший погляд, велика кількість умов та необхідність агрегувати одночасно за кількома критеріями для того, аби повернути єдиний результат, наводили на думку реалізації алгоритму процедурно на боці API, використовуючи юрбу if-then-else'ів. Але, як я зазначив на початку, концепція розробки на проекті вимагала іншого, тож для вирішення цієї задачі я подумки полишив територію імперативного програмування і перейшов на бік декларативного SQL.

І, хоча вимоги реалізації задачі у збереженій процедурі дозволяли виписати алгоритм напівімперативно, штибу "робимо попередню вибірку, аналізуємо, здобуваємось на якесь рішення, далі нова вибірка, аналіз, рішення і т.д", я вирішив реалізувати його максимально ескьюельно, давши собі відносно новий досвід того, як далеко може зайти суто декларативний підхід.

В підсумку я таки отримав, що хотів, єдиним SQL-запитом, і вирішення задачі таким способом вважаю певним особистим досягненням. Полюбляю задачі, для вирішення яких розум має торувати звивистий шлях серед багаточисельних думок та іноді неочікуваних можливостей.

Отже, сам SQL-запит має такий вигляд (діалект MS SQL):

CREATE PROCEDURE [dbo].[UpdateStakeholderOrganisationSentiment] @StakeholderOrganisationId int
AS
BEGIN
    DECLARE @ThresholdRED INT = 10;
    DECLARE @ThresholdGREEN INT = 80;

    DECLARE @SentimentChar as CHAR;

    WITH data(orgId, personId, sentiment)
             AS (SELECT so.StakeholderId as StakeholderOrganisationId,
                        sr.StakeholderId as StakeholderPersonId,
                        s.Sentiment
                 FROM StakeholderRelationship sr
                          INNER JOIN StakeholderOrganisation so
                                     ON sr.RelatedStakeholderId = so.StakeholderId
                                         AND sr.StakeholderRelationshipTypeId = 5 -- 'employee' relationship type
                                         AND sr.RelatedStakeholderId = @StakeholderOrganisationId
                          INNER JOIN Stakeholder s
                                     ON S.StakeholderId = sr.StakeholderId
                                         AND s.Sentiment != 'N'),
         percents(S, P) AS (SELECT sentiment, COUNT(sentiment) * 100 / (SELECT COUNT(*) FROM data) as Percentage
                            FROM data
                            GROUP BY sentiment),
         -- according to the current calculation logic the final Organization Sentiment value will have:
         -- R (RED) if number of RED Sentiments in the related Person stakeholders
         --  will be equal or more than @ThresholdRED percent.
         -- otherwise it will be G (GREEN) if number of GREEN Sentiments in the related Person stakeholders
         --  will be equal or more than @ThresholdGREEN percent.
         -- otherwise it will be A (AMBER) if there any AMBER Sentiments in the related Person stakeholders
         -- otherwise it will be N (NOT ENGAGED)
         result (c) AS (SELECT 'R' c
                        FROM percents
                        WHERE S = 'R'
                          AND P >= @ThresholdRED
                        UNION
                        SELECT 'G' c
                        FROM percents
                        WHERE S = 'G'
                          AND P >= @ThresholdGREEN
                        UNION
                        SELECT 'A' c
                        FROM percents
                        WHERE S = 'A'
                        UNION
                        SELECT '0')
    SELECT TOP 1 @SentimentChar = c
    FROM result
    ORDER BY c DESC

    UPDATE dbo.Stakeholder
    SET Sentiment = IIF(@SentimentChar = '0', 'N', @SentimentChar)
    WHERE StakeholderId = @StakeholderOrganisationId
END
go

Як видно, процедура має лише два положення: вирахування значення сентіменту та зберігання його в таблиці. І ось саме той великий запит, де відбувається вирахування значення сентіменту, і став найдовшим SQL-запитом, на який я досі здобувся :)

Власне, частково, окрім декларативності, той великий WITH-оператор дав мені враження роботи в парадигмі функціонального програмування, коли кожна його окрема частина, від початку до завершення, подготовлює результат для передачі наступній частині для подальшого деформування на шляху до єдиного остаточного значення. Тому розповідь про це здобуття і має назву "Мультипарадигменний SQL-запит"

Отже, перша частина оператора WITH підготовлює вибірку всіх стейкхолдерів-осіб, пов'язаних з заданим стейкхолдером-організацією, і іхніх сентіментів. Ця вибірка названа просто data. Друга частина повертає вибірку з назвою percents, яка містить процентні співвідношення різних сентиментів серед загалу знайдених у попередній частині записів. І, зрештою, остання частина result повертає те саме остаточне значення сентіменту для організації, і для його отримання й використавн невелику витівку — я зауважив, що зворотній алфавітний порядок символів, шо позначають сентіменти, співпадає з пріорітетом, за яким їх треба відбирати. Тобто, якщо є 'R',  то вже не треба брати 'G'; якщо є 'G', то вже немає потреба в 'A'. Тож якщо відсортувати вибірку всіх сентиментів у зворотньому порядку та взяти перший запис, отримаємо потрібний результат. З цього підходу вибивається лише літера 'N' для not engaged, але її я обійшов, впровадивши '0', який в таблиці кодування має найменше значення серед інших символів, що беруть участь в цьому алгоритмі.

Дякую за увагу!