ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Ругается на Coalese внутри рекурсивного запроса
4 сообщений из 4, страница 1 из 1
Ругается на Coalese внутри рекурсивного запроса
    #39387278
Егоров Иван
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Егоров Иван
Гость
Есть поле timing формата json в котором по дня указывается время начала и конца рабочего дня.
Для примера выглядит так:
Код: sql
1.
{"2": {"end": "00:00", "start": "12:00"}, "3": {"end": "00:00", "start": "12:00"}, "6": {"end": "00:00", "start": "12:00"}, "7": {"end": "00:00", "start": "12:00"}, "10": {"end": "00:00", "start": "12:00"}, "11": {"end": "00:00", "start": "12:00"}, "14": {"end": "00:00", "start": "12:00"}, "15": {"end": "00:00", "start": "12:00"}, "18": {"end": "00:00", "start": "12:00"}, "19": {"end": "00:00", "start": "12:00"}, "22": {"end": "00:00", "start": "12:00"}, "23": {"end": "00:00", "start": "12:00"}, "26": {"end": "00:00", "start": "12:00"}, "27": {"end": "00:00", "start": "12:00"}, "30": {"end": "00:00", "start": "12:00"}, "31": {"end": "00:00", "start": "12:00"}}



Мне нужно посчитать кол-во рабочих часов для сотрудника за все дни месяца.

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
SELECT 
		COALESCE(@extract(hours from (((timing::json ->> '1')::json ->> 'end')::time - ((timing::json ->> '1')::json ->> 'start')::time)),0)
		+COALESCE(@extract(hours from (((timing::json ->> '2')::json ->> 'end')::time - ((timing::json ->> '2')::json ->> 'start')::time)),0)
		+COALESCE(@extract(hours from (((timing::json ->> '3')::json ->> 'end')::time - ((timing::json ->> '3')::json ->> 'start')::time)),0)
		+COALESCE(@extract(hours from (((timing::json ->> '4')::json ->> 'end')::time - ((timing::json ->> '4')::json ->> 'start')::time)),0)
		+COALESCE(@extract(hours from (((timing::json ->> '5')::json ->> 'end')::time - ((timing::json ->> '5')::json ->> 'start')::time)),0)
		+COALESCE(@extract(hours from (((timing::json ->> '6')::json ->> 'end')::time - ((timing::json ->> '6')::json ->> 'start')::time)),0)
		+COALESCE(@extract(hours from (((timing::json ->> '7')::json ->> 'end')::time - ((timing::json ->> '7')::json ->> 'start')::time)),0)
		+COALESCE(@extract(hours from (((timing::json ->> '8')::json ->> 'end')::time - ((timing::json ->> '8')::json ->> 'start')::time)),0)
		+COALESCE(@extract(hours from (((timing::json ->> '9')::json ->> 'end')::time - ((timing::json ->> '9')::json ->> 'start')::time)),0)
		+COALESCE(@extract(hours from (((timing::json ->> '10')::json ->> 'end')::time - ((timing::json ->> '10')::json ->> 'start')::time)),0)
		+COALESCE(@extract(hours from (((timing::json ->> '11')::json ->> 'end')::time - ((timing::json ->> '11')::json ->> 'start')::time)),0)
		+COALESCE(@extract(hours from (((timing::json ->> '12')::json ->> 'end')::time - ((timing::json ->> '12')::json ->> 'start')::time)),0)
		+COALESCE(@extract(hours from (((timing::json ->> '13')::json ->> 'end')::time - ((timing::json ->> '13')::json ->> 'start')::time)),0)
		+COALESCE(@extract(hours from (((timing::json ->> '14')::json ->> 'end')::time - ((timing::json ->> '14')::json ->> 'start')::time)),0)
		+COALESCE(@extract(hours from (((timing::json ->> '15')::json ->> 'end')::time - ((timing::json ->> '15')::json ->> 'start')::time)),0)
		+COALESCE(@extract(hours from (((timing::json ->> '16')::json ->> 'end')::time - ((timing::json ->> '16')::json ->> 'start')::time)),0)
		+COALESCE(@extract(hours from (((timing::json ->> '17')::json ->> 'end')::time - ((timing::json ->> '17')::json ->> 'start')::time)),0)
		+COALESCE(@extract(hours from (((timing::json ->> '18')::json ->> 'end')::time - ((timing::json ->> '18')::json ->> 'start')::time)),0)
		+COALESCE(@extract(hours from (((timing::json ->> '19')::json ->> 'end')::time - ((timing::json ->> '19')::json ->> 'start')::time)),0)
		+COALESCE(@extract(hours from (((timing::json ->> '20')::json ->> 'end')::time - ((timing::json ->> '20')::json ->> 'start')::time)),0)
		+COALESCE(@extract(hours from (((timing::json ->> '21')::json ->> 'end')::time - ((timing::json ->> '21')::json ->> 'start')::time)),0)
		+COALESCE(@extract(hours from (((timing::json ->> '22')::json ->> 'end')::time - ((timing::json ->> '22')::json ->> 'start')::time)),0)
		+COALESCE(@extract(hours from (((timing::json ->> '23')::json ->> 'end')::time - ((timing::json ->> '23')::json ->> 'start')::time)),0)
		+COALESCE(@extract(hours from (((timing::json ->> '24')::json ->> 'end')::time - ((timing::json ->> '24')::json ->> 'start')::time)),0)
		+COALESCE(@extract(hours from (((timing::json ->> '25')::json ->> 'end')::time - ((timing::json ->> '25')::json ->> 'start')::time)),0)
		+COALESCE(@extract(hours from (((timing::json ->> '26')::json ->> 'end')::time - ((timing::json ->> '26')::json ->> 'start')::time)),0)
		+COALESCE(@extract(hours from (((timing::json ->> '27')::json ->> 'end')::time - ((timing::json ->> '27')::json ->> 'start')::time)),0)
		+COALESCE(@extract(hours from (((timing::json ->> '28')::json ->> 'end')::time - ((timing::json ->> '28')::json ->> 'start')::time)),0)
		+COALESCE(@extract(hours from (((timing::json ->> '29')::json ->> 'end')::time - ((timing::json ->> '29')::json ->> 'start')::time)),0)
		+COALESCE(@extract(hours from (((timing::json ->> '30')::json ->> 'end')::time - ((timing::json ->> '30')::json ->> 'start')::time)),0)
		+COALESCE(@extract(hours from (((timing::json ->> '31')::json ->> 'end')::time - ((timing::json ->> '31')::json ->> 'start')::time)),0)

FROM
		public.staffing_elements se
        
WHERE	
		se.person_id = 420
        AND se.staffing_id = 262



Такой вариант работает, но он слишком громоздкий.

Решил попробовать сделать рекурсивный запрос

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
WITH RECURSIVE r AS (

    SELECT 
        1 AS i
        ,0 AS sum
        ,person_id
    FROM
		public.staffing_elements se
	WHERE	
		se.person_id = 420
        AND se.staffing_id = 262
   
    UNION 
    

    SELECT 
        sum+СOALESCE(@extract(hours from (((se.timing::json ->> 'i')::json ->> 'end')::time - ((se.timing::json ->> 'i')::json ->> 'start')::time))::int,0) as sum,
        i+1 AS i
        ,se.person_id
    FROM
		public.staffing_elements se
        join r on r.person_id = se.person_id
	WHERE i < 31
)

select * from r



Ругается на COALESCE
авторERROR: function Сoalesce(integer, integer) does not exist
LINE 1: ...d = 420 AND se.staffing_id = 262 UNION SELECT sum+СOALESCE(@...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.

Без неё запрос выполняется выполняется.

В чём причина?
...
Рейтинг: 0 / 0
19.01.2017, 01:29
| Ответить | Цитировать | Написать  
Ругается на Coalese внутри рекурсивного запроса
    #39387290
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
Егоров Иван,

1) Крайне странно, у вас точно обычный postgresql а не какой то форк недоделанный?

2) Hm у вас там случайно COALESCE не с русской С в начале? Очень на то похоже.

PS: чтобы наверняка поставьте coalesce (lower case + английские буквы да).

--
Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
19.01.2017, 03:52
| Ответить | Цитировать | Написать  
Ругается на Coalese внутри рекурсивного запроса
    #39387647
grgdvo
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
grgdvo
Гость
Егоров Иван,

зачем сразу изводить себя такими запросами, чемодан коалесков, рекурсивные запросы....
Может чуть проще, у ПГ есть прекрасные функции для работы с JSON, превращаем его нехитрыми манипуляциями в табличку и считаем любую сумму, которая требуется
(маленькое неудобство - потребуется промежуточный тип создать).

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
create type startend as ("start" time, "end" time);

select
day_num, -- день
vstart, -- начало смены
vend, -- конец смены
vstart - vend, -- интервал между концом и началом
sum(vstart - vend) over () as sumtime -- подсчет суммы оконной функцией (можно сразу итог посчитать)
from
(select day_num, (se)."start" as vstart, (se)."end" as vend from
(select key as day_num, json_populate_record(null::startend, value) as se from
json_each(
'{"2": {"end": "00:00", "start": "12:00"}, "3": {"end": "00:00", "start": "12:00"}, "6": {"end": "00:00", "start": "12:00"}, "7": {"end": "00:00", "start": "12:00"}, "10": {"end": "00:00", "start": "12:00"}, "11": {"end": "00:00", "start": "12:00"}, "14": {"end": "00:00", "start": "12:00"}, "15": {"end": "00:00", "start": "12:00"}, "18": {"end": "00:00", "start": "12:00"}, "19": {"end": "00:00", "start": "12:00"}, "22": {"end": "00:00", "start": "12:00"}, "23": {"end": "00:00", "start": "12:00"}, "26": {"end": "00:00", "start": "12:00"}, "27": {"end": "00:00", "start": "12:00"}, "30": {"end": "00:00", "start": "12:00"}, "31": {"end": "00:00", "start": "12:00"}}'
)) q1) q2;



"2""12:00:00""00:00:00""12:00:00""192:00:00""3""12:00:00""00:00:00""12:00:00""192:00:00""6""12:00:00""00:00:00""12:00:00""192:00:00""7""12:00:00""00:00:00""12:00:00""192:00:00""10""12:00:00""00:00:00""12:00:00""192:00:00""11""12:00:00""00:00:00""12:00:00""192:00:00""14""12:00:00""00:00:00""12:00:00""192:00:00""15""12:00:00""00:00:00""12:00:00""192:00:00""18""12:00:00""00:00:00""12:00:00""192:00:00""19""12:00:00""00:00:00""12:00:00""192:00:00""22""12:00:00""00:00:00""12:00:00""192:00:00""23""12:00:00""00:00:00""12:00:00""192:00:00""26""12:00:00""00:00:00""12:00:00""192:00:00""27""12:00:00""00:00:00""12:00:00""192:00:00""30""12:00:00""00:00:00""12:00:00""192:00:00""31""12:00:00""00:00:00""12:00:00""192:00:00"
...
Рейтинг: 0 / 0
19.01.2017, 14:29
| Ответить | Цитировать | Написать  
Ругается на Coalese внутри рекурсивного запроса
    #39387809
Егоров Иван
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Егоров Иван
Гость
grgdvo,

Спасибо за совет! Попробую Ваш вариант.

Maxim Boguk,

Вы были совершенно правы во втором варианте. Не понимаю даже как так вышло...
...
Рейтинг: 0 / 0
19.01.2017, 16:42
| Ответить | Цитировать | Написать  
4 сообщений из 4, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Ругается на Coalese внутри рекурсивного запроса
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&fpage=79&tid=1996747]:
 0ms
get settings:
 8ms
get forum list:
 18ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 34ms
get topic data:
 11ms
get forum data:
 2ms
get page messages:
 50ms
get tp. blocked users:
 2ms
others: 387ms
total:  518ms
созд. / загр.: 518ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]