sorry, it´s a missing part of the CTE
that constant should be on beginning part of it.
with feriados as (
SELECT dia, repete
FROM (
VALUES ('2014-10-11'::DATE, FALSE), ('2014-10-13', FALSE),
('2014-10-14', FALSE), ('2014-10-15', FALSE), ('2014-10-16', FALSE),
('2014-10-17', FALSE), ('2014-12-19', FALSE), ('2014-12-20',
FALSE), ('2014-12-22', FALSE), ('2014-12-23', FALSE),
('2014-12-24', FALSE), ('2014-12-26', FALSE), ('2014-12-27',
FALSE), ('2014-12-29', FALSE), ('2014-12-30', FALSE),
('2014-12-31', FALSE), ('2015-01-02', FALSE), ('2015-01-03',
FALSE), ('2015-01-04', FALSE), ('2015-02-16', FALSE),
('2015-02-17', FALSE), ('2015-04-03', FALSE), ('2015-04-04',
FALSE), ('2015-06-04', FALSE), ('2015-12-18', FALSE),
('2015-12-19', FALSE), ('2015-12-21', FALSE), ('2015-12-22',
FALSE), ('2015-12-23', FALSE), ('2015-12-24', FALSE),
('2015-12-26', FALSE), ('2015-12-28', FALSE), ('2015-12-29',
FALSE), ('2015-12-30', FALSE), ('2015-12-31', FALSE),
('2016-01-02', FALSE), ('2016-01-04', FALSE), ('2016-01-05',
FALSE), ('2016-01-06', FALSE), ('2016-01-07', FALSE),
('2016-01-08', FALSE), ('2016-01-09', FALSE), ('2016-02-08',
FALSE), ('2016-03-09', FALSE), ('2016-03-25', FALSE),
('2016-03-26', FALSE), ('2016-05-26', FALSE), ('2016-12-24',
FALSE), ('2016-12-28', FALSE), ('2016-12-29', FALSE),
('2016-12-30', FALSE), ('2016-12-31', FALSE), ('2017-01-02',
FALSE), ('2017-01-03', FALSE), ('2017-01-04', FALSE),
('2017-01-05', FALSE), ('2017-01-06', FALSE), ('2017-01-07',
FALSE), ('2017-02-25', FALSE), ('2017-02-27', FALSE),
('2017-03-09', FALSE), ('2017-04-14', FALSE), ('2017-04-15',
FALSE), ('2017-06-15', FALSE), ('2017-09-30', FALSE),
('2017-12-18', FALSE), ('2017-12-19', FALSE), ('2017-12-20',
FALSE), ('2017-12-21', FALSE), ('2017-12-22', FALSE),
('2017-12-23', FALSE), ('2017-12-26', FALSE), ('2017-12-27',
FALSE), ('2017-12-28', FALSE), ('2017-12-29', FALSE),
('2017-12-30', FALSE), ('2018-01-02', FALSE), ('2018-01-03',
FALSE), ('2018-01-04', FALSE), ('2018-01-05', FALSE),
('2018-01-06', FALSE), ('2018-01-07', FALSE), ('2018-02-12',
FALSE), ('2018-02-13', FALSE), ('2018-03-30', FALSE),
('2018-03-31', FALSE), ('2018-04-30', FALSE), ('2018-05-31',
FALSE), ('2018-10-15', FALSE), ('2018-12-18', FALSE),
('2018-12-19', FALSE), ('2018-12-20', FALSE), ('2018-12-21',
FALSE), ('2018-12-22', FALSE), ('2018-12-24', FALSE),
('2018-12-26', FALSE), ('2018-12-27', FALSE), ('2018-12-28',
FALSE), ('2018-12-29', FALSE), ('2018-12-31', FALSE),
('2019-01-01', TRUE), ('2019-01-02', FALSE), ('2019-01-03',
FALSE), ('2019-01-04', FALSE), ('2019-01-05', FALSE),
('2019-01-07', FALSE), ('2019-02-09', TRUE), ('2019-03-09',
TRUE), ('2019-04-21', TRUE), ('2019-05-01', TRUE),
('2019-09-07', TRUE), ('2019-10-12', TRUE), ('2019-11-02', TRUE),
('2019-11-15', TRUE), ('2019-12-19', TRUE),
('2019-12-20', TRUE), ('2019-12-21', TRUE), ('2019-12-22', TRUE),
('2019-12-23', TRUE), ('2019-12-25', TRUE),
('2019-12-26', TRUE), ('2019-12-27', TRUE)
) x (dia, repete)
), materias as (
SELECT * from (VALUES
(593, 11091, 'AAC - Ética e Segurança Digital', 9, 120,
'2019/01/30'::DATE, '2019/01/30'::DATE, 60, 120, 0),
(593, 11085, 'AAC - Computação nas Nuvens', 12, 60, NULL, NULL, 60, 120,
60)
) x (turma_id, materia_id, materia, sequencia, previsto, dataini, datafim,
tempoatividade, minutosaula, minutosrestantes)
)
, col_diasaula(turma_id, dia, tempoaula) as(
select * from (values(593, 3, time '02:00')
) as x(turma_id, dia, tempoaula)),
), aulasporsemana as (
select * from (values (593,1)) x (turma_id, quantidade)
), assistidas (turma_id, sequencia, conteudo_id, conteudo, prevista,
aularealizada, tempoatividade, aulasNoDia, dia, minutoaula,
minutosassistidos, cargaconteudo, cargarestante) as (
SELECT
materias.turma_id,
materias.sequencia,
materias.materia_id,
materias.materia,
coalesce(realizada.prevista, 1),
realizada.aularealizada,
materias.tempoatividade,
(realizada.minutosassistidos / materias.tempoatividade),
realizada.dia,
materias.minutosaula,
realizada.minutosassistidos,
materias.previsto,
coalesce(materias.previsto - (row_number() OVER AulaDaMateria *
realizada.minutosassistidos), materias.previsto)
FROM materias
LEFT JOIN LATERAL (
SELECT TRUE, tsrange(col_aula.data, (col_aula.data + ( col_aula.tempo ||
' minute')::INTERVAL)) dia, 0 prevista, (extract(EPOCH FROM col_aula.tempo)
/ 60) minutosassistidos
FROM (VALUES
(593, 11091, TIMESTAMP '2019-01-30 19:00:00', TIME '02:00'),
(593, 11091, '2019-02-06 19:00:00', '01:00')
) col_aula (turma_id, materia_id, data, tempo)
WHERE col_aula.materia_id = materias.materia_id AND col_aula.turma_id =
materias.turma_id
ORDER BY col_aula.data, sequencia
) AS realizada(aularealizada, dia, prevista, minutosassistidos) ON TRUE
WINDOW
AulaDaMateria as (PARTITION BY materias.materia_id ORDER BY
materias.turma_id, materias.datafim NULLS LAST, materias.dataini NULLS LAST,
materias.sequencia, materias.materia_id),
AulaDia as (PARTITION BY materias.materia_id, realizada.dia)
ORDER BY turma_id, datafim NULLS LAST, dataini NULLS LAST, sequencia,
materia_id
) SELECT * FROM (
with recursive aulas as (
SELECT
turma_id,
aularealizada,
coalesce(coalesce(minutosassistidos, 0) + lag(minutosassistidos, 1)
over aulas_realizar, 0) > cargaconteudo irregular,
coalesce(coalesce(minutosassistidos, 0) + lag(minutosassistidos, 1)
over aulas_realizar, 0) assistido_ate_agora,
CASE
WHEN prevista = 0 AND NOT (coalesce(coalesce(minutosassistidos, 0) +
lag(minutosassistidos, 1) OVER aulas_realizar, 0) > cargaconteudo) THEN
(cargaconteudo / tempoatividade) - (lag(aulasNoDia, 1) OVER
aulas_realizar + aulasNoDia)
WHEN prevista = 1 THEN
(cargaconteudo / tempoatividade)
ELSE 0
END aulas,
case
when prevista = 0 and not (coalesce(coalesce(minutosassistidos, 0) +
lag(minutosassistidos, 1) over aulas_realizar, 0) > cargaconteudo) then
(cargaconteudo / tempoatividade) - (lag(aulasNoDia, 1) over
aulas_realizar + aulasNoDia)
else 1
END proxima,
prevista,
upper(dia) ultimadata,
conteudo_id,
conteudo,
cargaconteudo,
cargarestante,
tempoatividade,
dia,
minutosassistidos,
minutoaula,
sequencia
FROM assistidas
JOIN aulasporsemana USING (turma_id)
WINDOW aulas_realizar AS (PARTITION BY conteudo_id)
UNION
select
turma_id, datas.aularealizada, irregular, assistido_ate_agora, aulas,
proxima, prevista, ultimadata, datas.conteudo_id, conteudo, cargaconteudo,
datas.cargarestante, tempoatividade, dia, datas.minutosassistidos,
minutoaula, sequencia
from aulas c
JOIN LATERAL (
select
Format('%s week', coalesce(c.aulas, 0)) semanas,
false aularealizada,
c.conteudo_id,
tsrange(generate_series, generate_series + (minutoaula|| '
minute')::interval) diacalculado,
cargarestante - (row_number() OVER () * (extract(epoch from
col_diasaula.tempoaula) / 60)) cargarestante,
(case c.prevista when 1 then row_number() OVER () else 1 end *
(extract(epoch from col_diasaula.tempoaula) / c.tempoatividade))
minutosassistidos
from
generate_series(c.ultimadata - interval '1 day', (c.ultimadata +
Format('%s week', coalesce(proxima, 0))::INTERVAL), '1 day')
join col_diasaula on col_diasaula.dia = (extract(dow from
generate_series) +1) and col_diasaula.turma_id = c.turma_id
) datas on TRUE
where datas.conteudo_id = c.conteudo_id and c.aulas is not null and
coalesce(c.proxima, -1) >= 0
) select * from aulas
) valores;
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
