Is correlation supported by AtScale Postgress SQL?

Diego Perea

My setup is: GCP BigQuery <=> AtScale (Non containerized) <=>  Looker
I am trying to implement correlation between two fields in Looker as a custom field. 

I tried the following in a LookML model:

#----------- test correlation:

measure: sum_test_1{
type: number
sql: ( SELECT ${TABLE}.{% parameter kpi %} + ${TABLE}."KPI_1" FROM ${TABLE} )
;;
}

measure: corr_test{
type: number
sql: (SELECT CORR( ${TABLE}.{% parameter kpi %}, ${TABLE}."KPI_1") FROM ${TABLE})
;;
}

With the outcomes:
1- sum_test_1. It works, it verifies that the two columns have valid data before computing correlation
2- corr_test. It produces the following error: 
"The PostgreSQL 9.5+ database encountered an error while running this query.

ERROR: Don't understand function: corr"
(See generated SQL at the bottom)
According to these results, it seems that  correlation is NOT supported by AtScale Postgress SQL.
Can anybody confirm this? is there another way I should try?

--------------------------------------------------------------------------------------------------------------------------------------
SELECT
    (DATE(ERO_Agnostic."1d")) AS "ERO_Agnostic.Time_Dimension_1d",
    ERO_Agnostic.VOLTE_AFR  AS "ERO_Agnostic.kpi_name",
    0.5 *  ERO_Agnostic.VOLTE_AFR
       AS "ERO_Agnostic.counter_corr_1",
    ( SELECT ERO_Agnostic.VOLTE_AFR + ERO_Agnostic."DL_THPT"  FROM ERO_Agnostic )
       AS "ERO_Agnostic.counter_corr_3",
    AVG(ERO_Agnostic."DL_THPT") AS "ERO_Agnostic.DL_THPT",
    (SELECT CORR( ERO_Agnostic.VOLTE_AFR, ERO_Agnostic."DL_THPT") FROM ERO_Agnostic)
       AS "ERO_Agnostic.counter_corr_2"
FROM "ERO Agnostic"."ERO Agnostic" AS ERO_Agnostic
WHERE (ERO_Agnostic."Coverage_SiteID") = 'ON1344' AND ((( ERO_Agnostic."1d" ) >= (DATE_TRUNC('day', (DATE_TRUNC('day', CURRENT_TIMESTAMP AT TIME ZONE 'America/New_York') + (-6 || ' day')::INTERVAL))) AND ( ERO_Agnostic."1d" ) < (DATE_TRUNC('day', ((DATE_TRUNC('day', CURRENT_TIMESTAMP AT TIME ZONE 'America/New_York') + (-6 || ' day')::INTERVAL) + (7 || ' day')::INTERVAL)))))
GROUP BY
    1
ORDER BY
    1 DESC
0

Comments

1 comment

Date Votes
  • Comment author
    Brendanjpeterson

    You could try using the corr() function inside the model itself then add that calculated column as a measure 

    0

Please sign in to leave a comment.