Help with Group By

[eduardo_dimperio]

Hi Everyone,

I have a litle problem here. I have some values by hour and i need to get the lower hour (by hour)

Like

Input

1:00 am --- 100

1:03 am --- 120

1:50 am --- 190

2:02 am --- 200

2:27 am --- 230

2:45 am --- 280

output

1:00 am --- 100

2:02 am --- 200

I have this code:

LOAD

    DATE_READ,

    VALUE_READ,

    ID_SYSTEM&'|'&CONCENTRATOR&'|'&PORT AS ID_LEITURA

RESIDENT CONCAT_TABLE where DATE_READ>=TODAY()-2;

And tried this one, withou sucess.

LOAD

    DATE_READ,

    VALUE_READ,

    MIN(DATE_READ) AS MENOR_VALOR

RESIDENT CONCAT_TABLE where DATE_READ>=TODAY()-2

GROUP BY DATE_READ,VALUE_READ;

Someone could help me?

Thanks

[eduardo_dimperio]

Hi Marcus good morning, Still the same.

Can you please explain to me why group by doesn't group by date and for each date group filter the lowest value?

[eduardo_dimperio]

Hi Marcus, i think that works now and i understood the concept, i'll do more tests first and thanks for the help till now

[eduardo_dimperio]

I used the Marcus idea and change a little the code, now i understand the problem but dont know how to solve it.

If i dont use VALUE_READ in the group by i get no value and if i use i get more then one.

Any Ideia?

OUTPUT:

load

Floor(DATE_READ) as DATA,

Hour(DATE_READ) as HORA,  

VALUE_READ,

ID_SYSTEM&'|'&CONCENTRATOR&'|'&PORT AS ID_LEITURA

RESIDENT CONCAT_TABLE

  where DATE_READ>=TODAY()-2;

inner join (OUTPUT)

load

DATA,

HORA,

Min(VALUE_READ) as VALOR

resident OUTPUT

group by DATA,HORA;

DROP TABLE CONCAT_TABLE;

OUTPUT:

load

Floor(DATE_READ) as DATA,

Hour(DATE_READ) as HORA,   

VALUE_READ,

ID_SYSTEM&'|'&CONCENTRATOR&'|'&PORT AS ID_LEITURA

RESIDENT CONCAT_TABLE

  where DATE_READ>=TODAY()-2;

inner join (OUTPUT)

load

DATA,

HORA,

VALUE_READ,

Min(VALUE_READ) as VALOR

resident OUTPUT

group by DATA,HORA,VALUE_READ;

DROP TABLE CONCAT_TABLE;

[marcus_sommer]

I will try to simplify the approach from above and leave here the various transformations on the timestamp and the exact where-clause out:

Output:

load Date, Hour, Time, Value from Source where 1=1;

inner join (Output)

load Date, Hour, time(min(Time)) as Time

resident Output group by Date, Hour;

whereby the first part is a normal table-load (with some transformations and a where-clause). The second part loads the data from the first part grouped by Date and Hour so that this load will have max. 48 records (24 hours * 2 days) and min(Time) will return the min. Time for each of these day-hours-buckets. Aften this happens the join-statement which is an inner-join which meant that only those records that have a matching on all key-fields in both tables are left - an inner-join worked therefore like a where-clause and the conditions comes from an outside table-load.

Important by this approach are the (values within) the key-fields which are Date, Hour and Time - which you missed in your load-statement.

- Marcus

[eduardo_dimperio]

Hi Marcus, I didn't missed, but not worked for the first time or i dont quite understand.

and i still dont understand why my code doesn't work, if i have the same date, same hour and diferent values in VALUE_READ, why Min() doesn't work, what i missing?

Below your code adapted to my 2 variable DATE_READ(with contains the date of measures) and VALUE_READ(value read in the meter).

Again thank you for spend yout time to teaching me

Output:

load

Date(DATE_READ) AS DATA,

Hour(DATE_READ) AS HORA,

Time(DATE_READ) AS TEMPO,

VALUE_READ resident CONCAT_TABLE

where DATE_READ>=TODAY()-2;

inner join (Output)

load

DATA,

HORA,

time(min(TEMPO)) as TEMPO

resident Output group by DATA, HORA;

DROP TABLE CONCAT_TABLE;

Exit Script;

[marcus_sommer · Accepted solution]

Without the use of the floor/frac-function remain these fields timestamps like 42767,5422128472 for 01.02.2017  13:00:47 and date/time will just format it. Therefore try this:

Output:

load

Date(floor(DATE_READ)) AS DATA,

Hour(DATE_READ) AS HORA,

Time(frac(DATE_READ)) AS TEMPO,

VALUE_READ resident CONCAT_TABLE

where DATE_READ>=TODAY()-2;

inner join (Output)

load

DATA,

HORA,

time(min(TEMPO)) as TEMPO

resident Output group by DATA, HORA;

DROP TABLE CONCAT_TABLE;

Exit Script;

- Marcus

[eduardo_dimperio]

Hi Marcus,

I made some adjustments and finally it works.

Follow the final code and very thanks

LOAD

    VALUE_READ,

    ID_SYSTEM&'|'&CONCENTRATOR&'|'&PORT AS ID_LEITURA,

    Date(floor(DATE_READ)) AS DATA,

    Time(frac(DATE_READ)) AS TEMPO

RESIDENT CONCAT_TABLE where DATE_READ>=TODAY()-2;

//************************************OK

Output:

load

Date(floor(DATE_READ)) AS DATA,

Hour(DATE_READ) AS HORA,

Time(frac(DATE_READ)) AS TEMPO,

ID_SYSTEM&'|'&CONCENTRATOR&'|'&PORT AS ID_LEITURA,

VALUE_READ

resident CONCAT_TABLE

where DATE_READ>=TODAY()-2;

inner join (Output)

load

DATA,

HORA,

ID_LEITURA,

time(min(TEMPO)) as TEMPO

resident Output group by DATA, HORA,ID_LEITURA;

// //***************************************OK

NoConcatenate

VALOR:

LOAD

DATA,

HORA,

TEMPO,

ID_LEITURA,

Min(VALUE_READ) AS VALUE_READ

RESIDENT Output

Group By

DATA,

HORA,

TEMPO,

ID_LEITURA

;

Inner Join(TMP2)

Load

ID_LEITURA,

DATA,

VALUE_READ,

TEMPO

RESIDENT VALOR;

// // //**************************************

//DROP TABLE CONCAT_TABLE;

DROP TABLE Output;

DROP TABLE VALOR;