Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Ругается на Coalese внутри рекурсивного запроса / 4 сообщений из 4, страница 1 из 1
19.01.2017, 01:29
    #39387278
Егоров Иван
Егоров Иван
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ругается на Coalese внутри рекурсивного запроса
Есть поле 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, 03:52
    #39387290
Maxim Boguk
Maxim Boguk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ругается на Coalese внутри рекурсивного запроса
Егоров Иван,

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

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

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

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

зачем сразу изводить себя такими запросами, чемодан коалесков, рекурсивные запросы....
Может чуть проще, у ПГ есть прекрасные функции для работы с 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, 16:42
    #39387809
Егоров Иван
Егоров Иван
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ругается на Coalese внутри рекурсивного запроса
grgdvo,

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

Maxim Boguk,

Вы были совершенно правы во втором варианте. Не понимаю даже как так вышло...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Ругается на Coalese внутри рекурсивного запроса / 4 сообщений из 4, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 758ms
Закрыть
start [/forum/topic.php?fid=53&mobile=1&tid=1996747]:
 0ms
get settings:
 12ms
get forum list:
 11ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 41ms
get topic data:
 11ms
get forum data:
 2ms
get page messages:
 40ms
get tp. blocked users:
 2ms
others: 633ms
total:  758ms
x
x
Закрыть


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