SELECT, AVG, MIN - zapytanie SQL - pomocy

Witam,

mam taki przypadek, który przerasta moje możliwości. Szczegóły w załączniku i niżej na screenie:

https://imgshare.io/image/Ni6qLp

Chodzi głównie o to, że mam 3 okresy czasowe. Dla wszystkich danych należy wyliczyć średnią bez 0. Następnie dla każdego okresu wyznaczyć wartość minimalną powyżej 15% średniej. i na samym końcu wyznaczyć wartość maksymalną z tych 3 minimum. Czy jest to wykonalne za pomocą SQL? Próbowałem coś działać w tym kierunku ale udało mi się zrobić jedynie coś takiego:

sql SELECT MIN(measurementQuantity) from measurementsdaily m where m.measurementDate >= '2019-07-01' AND m.measurementDate <= '2019-07-10' UNION SELECT MIN(measurementQuantity) from measurementsdaily m where m.measurementDate >= '2019-07-11' AND m.measurementDate <= '2019-07-20' AND UNION SELECT MIN(measurementQuantity) from measurementsdaily m where m.measurementDate >= '2019-07-21' AND m.measurementDate <= '2019-07-31'

Powyższa formuła zwraca mi jedynie minimum dla każdego z 3 okresów.

Następnie próbowałem podczepić do tego 15% jako 0,15 i średnią ale to już nie działa

sql SELECT MIN(measurementQuantity) from measurementsdaily m where m.measurementDate >= '2019-07-01' AND m.measurementDate <= '2019-07-10' AND m.measurementQuantity >= '0.15'*AVG(NULLIF(measurementQuantity,0)) UNION SELECT MIN(measurementQuantity) from measurementsdaily m where m.measurementDate >= '2019-07-11' AND m.measurementDate <= '2019-07-20' AND m.measurementQuantity >= '0.15' UNION SELECT MIN(measurementQuantity) from measurementsdaily m where m.measurementDate >= '2019-07-21' AND m.measurementDate <= '2019-07-31' AND m.measurementQuantity >= '0.15'

Czy jest ktoś kto ogarnia i ma dobre serduszko?
Bardzo proszę o zerknięcie na problem.
Z góry dziękuję.

wersja na mssql, łatwe do przerobienia na inne silniki:

select top 1
   min(measurementQuantity) wynik
from (select 
        case 
            when measurementDate between '2019-07-01' and '2019-07-10' then 1
            when measurementDate between '2019-07-11' and '2019-07-20' then 2
            when measurementDate between '2019-07-21' and '2019-07-31' then 3
        end o
        ,* 
     from 
         tb) dt
 where 
    measurementQuantity > (select avg(measurementQuantity)*0.15 from tb where measurementQuantity>0)
group by
   o
 order by
    1 desc

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=d5d397910304e71b92536a0a76fd95dd

Panczo, Twoje rozwiązanie jest świetne i działa perfekcyjnie, tak jak oczekiwałem. Czy byłbyś tak miły pomóc mi to przerobić jeszcze na silnik MariaDB/MySQL?

Pewnie, dawaj co masz i robimy

Chodzi mi jedynie o przerobienie Twojego kodu na taki aby działał w MariaDB, ja niestety nie potrafię tego uczynić. Nie ukrywam, ze ratowałoby to mój tyłek :(

Ok, to może tutaj dalej opiszę o co chodzi. Mam taką tabelę w bazie jak niżej.
Fiddle: http://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=dca57a18489f9c669095f15b14b87e90

Potrzebuję wykorzystując tę bazę danych dodać wartości do innej tabeli dla każdego idPoint. Robię to za pomocą następującego zapytania:

const insertSql = `INSERT INTO detailsplan (
                idPoint,
                firstDegreeDay,
                secondDegreeDay,
                thirdDegreeDay,
                fourthDegreeDay,
                fifthDegreeDay,
                sixthDegreeDay,
                seventhDegreeDay,
                eighthDegreeDay,
                ninthDegreeDay,
                tenthDegreeDay,
                eleventhDegreeDay,
                twelfthDegreeDay)
                SELECT
                idPoint,
                contractedCapacity,
                AVG(NULLIF(measurementQuantity,0)),
                AVG(NULLIF(measurementQuantity,0))-(1*((AVG(NULLIF(measurementQuantity,0))-MIN(measurementQuantity))/8)),
                AVG(NULLIF(measurementQuantity,0))-(2*((AVG(NULLIF(measurementQuantity,0))-MIN(measurementQuantity))/8)),
                AVG(NULLIF(measurementQuantity,0))-(3*((AVG(NULLIF(measurementQuantity,0))-MIN(measurementQuantity))/8)),
                AVG(NULLIF(measurementQuantity,0))-(4*((AVG(NULLIF(measurementQuantity,0))-MIN(measurementQuantity))/8)),
                AVG(NULLIF(measurementQuantity,0))-(5*((AVG(NULLIF(measurementQuantity,0))-MIN(measurementQuantity))/8)),
                AVG(NULLIF(measurementQuantity,0))-(6*((AVG(NULLIF(measurementQuantity,0))-MIN(measurementQuantity))/8)),
                AVG(NULLIF(measurementQuantity,0))-(7*((AVG(NULLIF(measurementQuantity,0))-MIN(measurementQuantity))/8)),
                MIN(measurementQuantity),
                0,
                0}
                FROM measurementsdaily m WHERE m.measurementDate >= '${from}' AND m.measurementDate <= '${to}' GROUP BY idPoint`

gdzie from = 2019-07-01 i to = 2019-07-31

Jednak to zapytanie które napisałem jest błędne i mam z tym problemy jak napisać poprawnie. Linijkę, którą wstawiam do tabeli jako:

*secondDegreeDay czyli AVG(NULLIF(measurementQuantity,0)), - tutaj chciałem policzyć średnią bez zer ale dla okresu od 2019-07-21 do 2019-07-31 natomiast aktualnie liczy mi się średnia od 2019-07-01 do 2019-07-31 :(

*tenthDegreeDay czyli MIN(measurementQuantity), - tutaj chciałem policzyć to o czym pisałem wcześniej i co udało się zrobić tylko w mssql. Czyli liczenie dla 3 okresów czasowych tj. od 2019-07-01 do 2019-07-10, od 2019-07-11 do 2019-07-20 i od 2019-07-21 do 2019-07-31 wartości minimalnych powyżej 15% średniej. Gdzie średnia jest liczona bez zer ale dla okresu od 2019-07-01 do 2019-07-31. I spośród tych 3 minimum wybrać wartość maksymalną.

*między thirdDegreeDay a ninthDegreeDay, tu chodzi o wstawienie wartości pośrednich między secondDegreeDay a tenthDegreeDay. Czyli od tenthDegreeDay odejmujemy secondDegreeDay dzielimy na 8 i do każdego kolejnego DegreeDay dodajemy kolejną część. Przykład wyników:

Jeśli: thirdDegreeDay = 2 i tenthDegreeDay = 10 a więc (10-2)/8=1 dlatego

thirdDegreeDay = 3
fourthDegreeDay = 4
fifthDegreeDay = 5
sixthDegreeDay = 6
seventhDegreeDay = 7
eighthDegreeDay = 8
ninthDegreeDay = 9

Załączam również excel z oczekiwanymi wynikami i wrzucam screena:
https://imgshare.io/image/NiOITp

Proszę, błagam dobre dusze o pomoc :(

Poznaj moje dobre serce:

select
   idPoint
   ,max(m) `tenthDegreeDay`
   ,max(case when o=3 then avg15 else 0 end) `secondDegreeDay`
FROM (
   SELECT 
      O
      ,idPoint
      ,avg15
      , min(`measurementQuantity`) m
   FROM (select 
          case  
             when day( `measurementDate`) > 20 then 3
             when day( `measurementDate`) > 10 then 2
             when day( `measurementDate`) > 0 then 1
         END O
         ,measurementDate
         ,mmd.idPoint
         ,contractedCapacity
         ,measurementQuantity
         ,avg15
       from 
          `measurementsdaily` mmd
          inner join (select 
                     idPoint
                     ,avg(measurementQuantity)*0.15 avg15 
                  from 
                     `measurementsdaily` 
                  where 
                     measurementQuantity>0 
                     and measurementDate between '2019-07-01' and '2019-07-31'
                  group by
                     idPoint
                  ) mmd_avg on mmd_avg.idPoint = mmd.idPoint
       where 
         measurementQuantity > avg15
           and measurementDate between '2019-07-01' and '2019-07-31') dt
   group by
      o
      ,idPoint
      ,avg15) r
group by
    idPoint

https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=f1d889a2e108951401c4e88ccae758f1

Poprawka, źle wyliczałem kolumnę econdDegreeDay w poprzednim przykładzie

select
	idPoint
	,max(m) `tenthDegreeDay`
	,max(SDD) `secondDegreeDay`
FROM (
	SELECT 
		O
		,idPoint
		,avg15
		, min(`measurementQuantity`) m
		,sum(case when o=3 then measurementQuantity else null end)/sum(case when o=3 then 1 else null end) `SDD`
	FROM (select 
			 case  
				 when day( `measurementDate`) > 20 then 3
				 when day( `measurementDate`) > 10 then 2
				 when day( `measurementDate`) > 0 then 1
			END O
			,measurementDate
			,mmd.idPoint
			,contractedCapacity
			,measurementQuantity
			,avg15
		 from 
			 `measurementsdaily` mmd
			 inner join (select 
							idPoint
							,avg(measurementQuantity)*0.15 avg15 
						from 
							`measurementsdaily` 
						where 
							measurementQuantity>0 
							and measurementDate between '2019-07-01' and '2019-07-31'
						group by
							idPoint
						) mmd_avg on mmd_avg.idPoint = mmd.idPoint
		 where 
			measurementQuantity > avg15
     		and measurementDate between '2019-07-01' and '2019-07-31') dt
	group by
	   o
	   ,idPoint
	   ,avg15) r
group by
    idPoint

Panczo, trochę źle wytłumaczyłem i był błąd w excelu ale mimo to działa to co napisałeś idealnie i to ta 1 wersja kod, po naniesieniu przeze mnie kilku zmian:

1. Co prawda zwracane było 15% średniej ale obszedłem to trochę takim ułomnym sposobem. Dzielę przez 0,15 linijkę numer 4 i wszystko jest ok.
2. Błąd po mojej stronie średnia którą używa się w tenthDegreeDay powinna być z okresu 2019-07-21 do 2019-07-31 nie 2019-07-01 do 2019-07-31 tak jak to wcześniej napisałem. Poprawiłem więc linijkę 31.

select
	idPoint
	,max(m) `tenthDegreeDay`
	,max(case when o=3 then avg15/0.15 else 0 end) `secondDegreeDay`
FROM (
	SELECT 
		O
		,idPoint
		,avg15
		, min(`measurementQuantity`) m
	FROM (select 
			 case  
				 when day( `measurementDate`) > 20 then 3
				 when day( `measurementDate`) > 10 then 2
				 when day( `measurementDate`) > 0 then 1
			END O
			,measurementDate
			,mmd.idPoint
			,contractedCapacity
			,measurementQuantity
			,avg15
		 from 
			 `measurementsdaily` mmd
			 inner join (select 
							idPoint
							,avg(measurementQuantity)*0.15 avg15 
						from 
							`measurementsdaily` 
						where 
							measurementQuantity>0 
							and measurementDate between '2019-07-21' and '2019-07-31'
						group by
							idPoint
						) mmd_avg on mmd_avg.idPoint = mmd.idPoint
		 where 
			measurementQuantity > avg15
     		and measurementDate between '2019-07-01' and '2019-07-31') dt
	group by
	   o
	   ,idPoint
	   ,avg15) r
group by
    idPoint

Efektem jest, że wszystko super działa!!! I to dzięki Tobie. Wyniki są takie jak w excelu: https://imgshare.io/image/NilIld

Prośbę mam jednak taką, czy mógłbyś dorzucić do tego jeszcze wyliczanie tych thirdDegreeDay do ninthDegreeDay?
Czyli:
thirdDegreeDay = secondDegreeDay-1*((secondDegreeDay-tenthDegreeDay)/8)
fourthDegreeDay = secondDegreeDay-2*((secondDegreeDay-tenthDegreeDay)/8)
fifthDegreeDay = secondDegreeDay-3*((secondDegreeDay-tenthDegreeDay)/8)
sixthDegreeDay = secondDegreeDay-4*((secondDegreeDay-tenthDegreeDay)/8)
seventhDegreeDay = secondDegreeDay-5*((secondDegreeDay-tenthDegreeDay)/8)
eighthDegreeDay = secondDegreeDay-6*((secondDegreeDay-tenthDegreeDay)/8)
ninthDegreeDay = secondDegreeDay-7*((secondDegreeDay-tenthDegreeDay)/8)

Ogólnie to jesteś Bogiem, ratujesz mi życie i bardzo dobry człowiek z Ciebie!!! Dziękuję Ci za wszystko.