#sql #hana
#sql #hana
Вопрос:
Бизнесу нужно, чтобы неделя начиналась в воскресенье, но SAP HANA SQL WEEK() начинается в понедельник. Как мне начать календарную неделю в воскресенье? Я нашел несколько веб-страниц, указывающих на это как на проблему, подобную этой, но решения нет.
CALWEEK здесь уже в таблице, поэтому я бы сосредоточился на CALWEEK_DERIVED.
Ответ №1:
здесь начинается более короткий вариант, учитывающий угловой случай в year, без использования вспомогательной таблицы:
CREATE OR replace FUNCTION week_sunday(DATE date) RETURNS ret integer AS BEGIN
ret = CASE weekday(extract(YEAR FROM date)||'-01-01')
WHEN 6 THEN
CASE weekday(date)
WHEN 6 THEN week(date)
ELSE week(date)-1
END
ELSE
CASE weekday(date)
WHEN 6 THEN week(date) 1
ELSE week(date)
END
END;
END;
Ответ №2:
Сработало следующее. Обратите внимание, что производной неделей, начинающейся с воскресенья, является CALWEEK, и для облегчения проверки присутствуют другие столбцы.
Для этого решения требуется наличие «_SYS_BI».»M_TIME_DIMENSION», который, возможно, придется сгенерировать в соответствии с комментарием внизу этого веб-сайта.
Я включаю специальную обработку, когда год начинается в воскресенье, как в 2017 году — ищите WEEKDAY(TO_DATE(CONCAT(YEAR(date_sap),'-01-01'), 'YYYY-MM-DD')) = 6
Я рассмотрю ниже:
- Текущий 2020 год
- Крайний случай 1: когда год начинается в воскресенье
- Крайний случай 2: когда год начинается в субботу
- Проверка SQL с 1900 по 2100 год
Текущий 2020 год
-- Current year 2020 started on a Wednesday
select date_sap, YEAR(date_sap) as year_date_sap, WEEKDAY(date_sap) as weekday_date_sap, WEEK(date_sap) as SAPWEEK,
(CASE
-- subtract by 1 when year starts on a Sunday like in 2017
when WEEKDAY(TO_DATE(CONCAT(YEAR(date_sap),'-01-01'), 'YYYY-MM-DD')) = 6 and
((YEAR(ADD_DAYS(date_sap, 1)) = (YEAR(date_sap) 1)) and (WEEKDAY(date_sap) != 6)) THEN CONCAT(YEAR(date_sap), WEEK(date_sap) - 1)
when WEEKDAY(TO_DATE(CONCAT(YEAR(date_sap),'-01-01'), 'YYYY-MM-DD')) = 6 and
((YEAR(ADD_DAYS(date_sap, 1)) = (YEAR(date_sap) 1)) and (WEEKDAY(date_sap) = 6)) THEN CONCAT(YEAR(date_sap), WEEK(date_sap))
when WEEKDAY(TO_DATE(CONCAT(YEAR(date_sap),'-01-01'), 'YYYY-MM-DD')) = 6 and
YEAR(ADD_DAYS(date_sap, 1)) = (YEAR(date_sap)) THEN CONCAT(YEAR(date_sap), WEEK(ADD_DAYS(date_sap, 1)) - 1)
when ((YEAR(ADD_DAYS(date_sap, 1)) = (YEAR(date_sap) 1)) and (WEEKDAY(date_sap) != 6)) THEN CONCAT(YEAR(date_sap), WEEK(date_sap))
when ((YEAR(ADD_DAYS(date_sap, 1)) = (YEAR(date_sap) 1)) and (WEEKDAY(date_sap) = 6)) THEN CONCAT(YEAR(date_sap), WEEK(date_sap) 1)
when YEAR(ADD_DAYS(date_sap, 1)) = (YEAR(date_sap)) THEN CONCAT(YEAR(date_sap), WEEK(ADD_DAYS(date_sap, 1)))
END) AS CALWEEK
from "_SYS_BI"."M_TIME_DIMENSION"
-- to eliminate missing values (appears as "?")
where date_sap >= TO_DATE('2019-12-30', 'YYYY-MM-DD')
and date_sap <= TO_DATE('2020-01-14', 'YYYY-MM-DD')
order by date_sap;
Крайний случай 1: когда год начинается в субботу
Год 2011
-- Same SQL but use:
where date_sap >= TO_DATE('2010-12-20', 'YYYY-MM-DD')
and date_sap <= TO_DATE('2011-01-16', 'YYYY-MM-DD')
Крайний случай 2: Когда год начинается в воскресенье
2017 год
-- Same SQL but use:
where date_sap >= TO_DATE('2016-12-20', 'YYYY-MM-DD')
and date_sap <= TO_DATE('2017-01-16', 'YYYY-MM-DD')
Проверка SQL с 1900 по 2100 год
нужна возможность создания временной таблицы
-- need the ability to create a temporary table
drop table #CALWEEK_TEST;
create local temporary table #CALWEEK_TEST as (
select date_sap, YEAR(date_sap) as year_date_sap, WEEKDAY(date_sap) as weekday_date_sap, WEEK(date_sap) as SAPWEEK,
(CASE
-- subtract by 1 when week starts on a Sunday like in 2017
when WEEKDAY(TO_DATE(CONCAT(YEAR(date_sap),'-01-01'), 'YYYY-MM-DD')) = 6 and
((YEAR(ADD_DAYS(date_sap, 1)) = (YEAR(date_sap) 1)) and (WEEKDAY(date_sap) != 6)) THEN CONCAT(YEAR(date_sap), WEEK(date_sap) - 1)
when WEEKDAY(TO_DATE(CONCAT(YEAR(date_sap),'-01-01'), 'YYYY-MM-DD')) = 6 and
((YEAR(ADD_DAYS(date_sap, 1)) = (YEAR(date_sap) 1)) and (WEEKDAY(date_sap) = 6)) THEN CONCAT(YEAR(date_sap), WEEK(date_sap))
when WEEKDAY(TO_DATE(CONCAT(YEAR(date_sap),'-01-01'), 'YYYY-MM-DD')) = 6 and
YEAR(ADD_DAYS(date_sap, 1)) = (YEAR(date_sap)) THEN CONCAT(YEAR(date_sap), WEEK(ADD_DAYS(date_sap, 1)) - 1)
when ((YEAR(ADD_DAYS(date_sap, 1)) = (YEAR(date_sap) 1)) and (WEEKDAY(date_sap) != 6)) THEN CONCAT(YEAR(date_sap), WEEK(date_sap))
when ((YEAR(ADD_DAYS(date_sap, 1)) = (YEAR(date_sap) 1)) and (WEEKDAY(date_sap) = 6)) THEN CONCAT(YEAR(date_sap), WEEK(date_sap) 1)
when YEAR(ADD_DAYS(date_sap, 1)) = (YEAR(date_sap)) THEN CONCAT(YEAR(date_sap), WEEK(ADD_DAYS(date_sap, 1)))
END) AS CALWEEK
from "_SYS_BI"."M_TIME_DIMENSION"
-- to eliminate missing values (appears as "?")
where date_sap >= TO_DATE('1900-01-01', 'YYYY-MM-DD')
and date_sap <= TO_DATE('2100-12-31', 'YYYY-MM-DD')
order by date_sap
);
— Проверьте, всегда ли количество записей равно 1-7 за КАЛЕНДАРНУЮ НЕДЕЛЮ => УСПЕХ
select distinct count(*) as RECORDS_PER_CALWEEK from #CALWEEK_TEST
group by CALWEEK;
— Проверьте, нет ли каких-либо необычных значений CALWEEK (ожидаемых 1-53) => УСПЕХ
select distinct SUBSTRING(CALWEEK, 5) as WEEKONLY from #CALWEEK_TEST
order by WEEKONLY;
Ответ №3:
Создайте функцию:
CREATE OR REPLACE FUNCTION non_iso_week(in_date DATE)
RETURNS week INTEGER LANGUAGE SQLSCRIPT AS BEGIN
week := CASE EXTRACT(DOW FROM in_date)
WHEN 0 THEN WEEK(in_date) 1
ELSE WEEK(in_date)
END;
END;
Затем:
SELECT DISTINCT
calweek
, non_iso_week(order_date) AS calweek_derived
, order_date
FROM dummy
WHERE order_date >='2020-03-07'
AND order_date <='2020-03-10'
;
Комментарии:
1. Сбой при создании кода функции с помощью SAP DBTech JDBC: [257]: ошибка синтаксиса sql: неправильный синтаксис рядом с «RETURN»: строка 2 col 1 (на позиции 59).
2. дважды проверил синтаксис создания функции SAP HANA и соответствующим образом изменил
3. Я не думаю, что это сработает в 2011 году, если сравнить 1 января (субботу) со 2 января (воскресенье). Вы можете проверить?
4. Текущий код создания функции возвращает ошибку для меня: SAP DBTech JDBC: [257]: ошибка синтаксиса sql: неправильный синтаксис рядом с «DOW»: строка 3, страница 24 (на позиции 129)
5. должно быть
WEEKDAY(date)
вместоEXTRACT(DOW FROM date)
, но это решение не учитывает угловой случай в начале / конце года