SQLScript Heavy Lifting

[Former Member]

Hi,

I need your opinion on how to code the following view using SQL. This is for a research project on HANA.

The table has sequential meter readings with the following table structure:

CustomerID, MeterID, Date, Time, Meter Reading

Now I need to know the difference from one reading to the next in order to calculate the amount of electricity consumed in that time period. So if I am taking out a trend over a year, I would need an average on the difference of sequential meter readings.

One idea is to do an inner join on the same table with an offset of 15 seconds and then minus the Meter Reading fields.

There will be millions of customer records in the table and this calculation needs to be done for different customers individually or as groups.

For example:

Date Time Reading

12-Feb 12:00 11000

12-Feb 1300 12000

My problem is that I cant apply a simple average function in SQL over the reading. Power consumed = 12000 - 11000 = 1000

Can you help?

Best Regards,

Adeel Hashmi

[lbreddemann]

Hello Adeel,

if you want to calculate the difference between two (subsequent) readings, than there's really no other option than this:

- for every entry you have to find the most current reading that is at least 15 seconds old

- calculate the difference for the reading values

This can be done with a join, that's right.

And you can use this as a subquery for your AVG query:

select meterid, avg(readdiff) as avg_diff from(
   select meterid, (reading_new - reading_old) as readdiff
  from 
    readings  ...)
group by meterid

Why can't you do that? Do you get any error?

regards,

Lars

p.s.

This query won't be a good one for the column store engine.

Unequal joins (as likely required here) are not executed very quickly.

It could be worthwhile to think about splitting the logic to a SQLScript procedure and to introduce an ever increasing readings-unique-id to make it easier to find the most current reading that is at least 15 seconds older than the current reading...

[Former Member]

Hi Lars,

The inner join idea has been proposed by a few friends but I know the column store optimization wont work on it. The problem is that the table contains data for millions of customers and so its not necessary that if we sort on the table's unique key field, it will provide the next entry for the same customer. Therefore, I will have to sort the table on customer, date and time to access the data.

I am willing to write the stored procedure and thanks for suggesting it, but first I am not familiar with stored procedures and second how writing one will help the column store. Hope you can provide some guidance.

Thanks!

Adeel

[lbreddemann]

> The inner join idea has been proposed by a few friends but I know the column store optimization wont work on it. 

??? Why do you think an inner join doesn't work for the column store?

Although this kind of join might not be the one that the column store is best at, but it still works!

The problem is that the table contains data for millions of customers and so its not necessary that if we sort on the table's unique key field, it will provide the next entry for the same customer. Therefore, I will have to sort the table on customer, date and time to access the data. 

I don't agree. At all.

This is not row processing.

What you need is for your current customer, for the current meter_id the most current readings that are at least one second older than your reference point.

For that you can create an additional index or a computed column.

No need to sort lots of data here.

Functions like MAX() can be well optimized.

> I am willing to write the stored procedure and thanks for suggesting it, but first I am not familiar with stored procedures and second how writing one will help the column store. Hope you can provide some guidance.

There is already guidance available: [https://help.sap.com/hana_appliance#section5] -> "SAP HANA - Development Guide" and "SAP HANA Database - SQL Script Guide".

The development guide also contains examples on how to create SQLScript.

Hope that pushes you into the right direction.

Lars

[Former Member]

Dear Lars,

I have been pushed in the right direction. Read through HANA SQL Guide and Reference files. The following code is what I have come up but its not compiling / activating in HANA. Could you glance through it please?

var_out = select KUNNR_1 "KUNNR_1", ZDATE "ZDATE", avg(reading-old_reading) "READING"

(

select reading MAX(time) as old_reading

from "_SYS_BIC"."prj-electricity/ANA_MASTER" as B

)

from "_SYS_BIC"."prj-electricity/ANA_MASTER" as A

where a.zdate = b.zdate and b.time < a.time

group by kunnr_1 zdate;

Thanks.

Regards,

Adeel Hashmi

[lbreddemann]

Hi Adeel,

if you want me to review/debug your code, please provide al the DDL/DML statements required to reproduce (that includes creating the table and inserting the data).

I'm pretty busy working in SAP support and answering questions and I just cannot take the time to rebuild your example from scratch on my own.

Also, if you get any errors, please provide the error message completely as well.

Thanks and regards,

Lars

[Former Member]

Hi,

For the benefit of others reading this post, the issue was in posing the right query to HANA. A colleague helped out in writing the following script which took care of all the complex requirements. Basically we put the select after the FOR statement. See below. Thanks.

select avg(s.ReadDiff)

From

(select readdate, max(reading)-min(reading) AS ReadDiff from customerreading

where readdate between '02/01/2012' and '02/05/2012'

and readtime between '1/1/1900 06:00:00 AM' and '1/1/1900 12:00:00 PM'

and custid=1 group by readdate) S

Regards,

Adeel Hashmi