NTILE function in HANA SQL

[Former Member]

Hi All,

I need to write an SQL in HANA, which would produce a similar result to an NTILE() in SQL 2008.

as per my knowledge NTILE function does not exist in HANA SQL. Has anybody written an SQL similar to this function?

e.g below the MSSQL 2008 code, which need to be re-written in HANA SQL.

select case when FLD1 in ('AA','XX')

then 'BB'

      else FLD1 end as FLD1,

  NTILE(3) Over (PARTITION by case when FLD1 in

   ('AA','XX')  then 'BB'

      else FLD1 end

   Order by FLD_Dis Asc) as Percentile,

  FLD_Dis,

  'ALL' as FLD2,

  'ALL' as FLD3,

  From

"SCHEMA"."TABLE1"

  where FLD2 >= 10

GROUP BY FLD3, FLD1, FLD2,FLD_Dis

   Order by 1,2,3,4;

Thanks

Ranjit

[lbreddemann · Accepted solution]

Hello Ranjit,

no idea what your timeframe is, but window functions (like NTILE, SUM, RANK, LAG/LEAD...) are being developed and will find their ways into HANA standard.

So maybe it pays to wait for this.

However, I can't tell when these functions will be available.

Cheers,

Lars

ADDITIONAL INFORMATION:

Just had a look into the current SQL reference manual which is already out for SPS5: http://help.sap.com/hana/html/_esql_functions_window.html

As you see, window functions will be available on a HANA system near you soon 😉

Message was edited by: Lars Breddemann

[uwe_kohlmann · Accepted solution]

Hi Lars,

do you know whether there is an update on this? I don't find an up-to-date refference to ntile and rank functions

Cheers,

Uwe

[lucas_oliveira · Accepted solution]

Hi,

Please look into the official HANA documentation on window functions:

Window Functions - SAP HANA SQL and System Views Reference - SAP Library

NTILE and RANK are available.

BRs,

Lucas de Oliveira

[uwe_kohlmann · Accepted solution]

Thx Lucas!

exactly what I was looking for ... event though the reference is a bit too technical for business users. But I'll find my way ...

Cheers,

Uwe