Skip to content

Instantly share code, notes, and snippets.

@des1roer
Created May 5, 2016 05:17
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save des1roer/4e0c4cc9a86d2be67c6eebf83eb42e2c to your computer and use it in GitHub Desktop.
Save des1roer/4e0c4cc9a86d2be67c6eebf83eb42e2c to your computer and use it in GitHub Desktop.
SELECT c.lvl,
sum(c.q) AS q,
sum(coalesce(c.q * fe.value,0))/sum(c.q) AS fe,
sum(coalesce(c.q * zn.value,0))/sum(c.q) AS zn,
sum(coalesce(c.q * s.value,0))/sum(c.q) AS s
FROM
(SELECT d.value AS q,
c.id,
EXTRACT (hour
FROM d.f_timestamp), lvl.value AS lvl,
d.f_timestamp AS time,
t.name_id
FROM
(SELECT *
FROM vgok_site.a_analiz_data
WHERE elem_id = 6) d,
(SELECT *
FROM vgok_site.a_analiz_data
WHERE elem_id = 84) lvl,
vgok_site.a_analiz_create c,
vgok_site.a_template t
WHERE d.analiz_id = c.id
AND lvl.analiz_id = c.id
AND c.template_id = t.id
AND t.id = 77
AND d.value IS NOT NULL
AND '2016-05-05' = to_char(c.f_timestamp, 'YYYY-MM-DD')) c
LEFT JOIN
(SELECT *
FROM vgok_site.a_analiz_data fe
WHERE fe.elem_id = 33) fe ON fe.analiz_id = c.id
LEFT JOIN
(SELECT *
FROM vgok_site.a_analiz_data zn
WHERE zn.elem_id = 2) zn ON zn.analiz_id = c.id
LEFT JOIN
(SELECT *
FROM vgok_site.a_analiz_data s
WHERE s.elem_id = 8) s ON s.analiz_id = c.id
GROUP BY c.lvl
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment