This function returns the coefficient of correlation when passed a pair of numbers. CORR will determine if a relationship exists between the pair of values it receives. The result is a range from −1 to 1 where either ±1 means there is a correlation between the two numbers, and a 0 means there is no correlation. An interesting check using the brainwave data might be to determine if there is correlation between reading values for given scenarios. Note that there is no CORR support on SQL pools; therefore, you must run this on a Spark pool using SparkSQL. This table contains data for all electrodes and the frequency of BETA_H in columns based on the scenario.
+—————-+————–+————+————+——–+
| ClassicalMusic | WorkMeeting | Meditation | MetalMusic | TikTok |
+—————-+—————+———–+————+——–+
| 1.688 | 2.084 | 1.129 | 2.725 | 0.758 |
| 1.544 | 1.898 | 0.974 | 2.706 | 0.945 |
| 1.829 | 0.99 | 0.575 | 1.781 | 1.139 |
| … | … | … | … | … |
+—————-+————–+————+————+——–+
To make some sense of the results, it would help to know that the BETA_H frequency is linked to scenarios that require concentration. Looking at the output you might conclude relative to ClassicalMusic, watching TikTok perhaps does not take as much concentration. That conclusion can be made because the values are higher for ClassicalMusic than for TikTok. However, there is not enough rendered data to make a real conclusion; it is just a first impression. To check to see if there is a correlation between any two of those columns, you can use CORR, as in the following example:
%%sql
SELECT CORR(_c1, _c5) AS Correlation FROM BETA_H
The BETA_H frequency correlation coefficient between ClassicalMusic and TiKTok is 0.03299257. The CORR clause returns the Pearson correlation coefficient, and how to interpret this output is worthy of a book in itself. However, it is safe to say that there is a small positive correlation between the two scenarios.
CROSS APPLY
Use this command to make a cross‐reference to rows from a subquery. CROSS APPLY is the equivalent to a LATERAL JOIN. The following code snippet illustrates the implementation of CROSS APPLY. You can identify a subquery by the existence of multiple SELECT statements.
SELECTS.SESSION_ID AS SESSION_ID, AGE_IN_YEARS,DATEADD(year, (AGE_IN_YEARS + 1), SESSION_DATETIME) AS NEXT_ANNIVERSARY,DATEDIFF(day, GETDATE(), DATEADD(year, (AGE_IN_YEARS + 1),SESSION_DATETIME)) AS DAYS_TO_NEXT_ANNIVERSARYFROM [SESSION] SCROSS APPLY(SELECT FLOOR(DATEDIFF(week, S.SESSION_DATETIME,GETDATE()) / 52.177457) AS AGE_IN_YEARS) AS T
ORDER BY SESSION_ID
This query uses the SESSION_DATETIME value, which exists on the SESSION table to determine how old the session is in years. Notice that the AGE_IN_YEARS is produced in the subquery, but it is referenced from the main query. This is the power of CROSS APPLY because results from the subquery can be used in the main query during its execution. You might notice the DATEDIFF() function, which will return the number of days between two dates. You can pass year, quarter, month, day, week, and so on as the first parameter and DATEDIFF() will return the number of years, months, weeks, whatever you passed it back. FLOOR() returns the integer part of the number that is passed to it as a parameter; basically, it rounds the number down to the nearest integer. You will see CROSS APPLY again later when we discuss OPENROWSET, which is commonly used when working with JSON files.
COPY INTO
This command is similar to BULK INSERT, but COPY INTO is optimized and only supported on Azure Synapse Analytics. It is supported on dedicated SQL pools; you cannot run COPY INTO on the built‐in serverless SQL instance at this time. Here’s the simplest example of using this SQL command:
COPY INTO READINGCSV
FROM ‘https://?.blob.core.windows.net/brainjammer/<path>’
WITH ( FIRSTROW = 2 )
This command assumes there is a table named READINGCSV that has a schema that matches the content of the CSV file. You can view the SQL for the READINGCSV table in the BrainwaveTable folder on GitHub here: https://github.com/benperk/ADE. Once the data is loaded, you can execute the SQL queries required to perform your analysis.
