Chúng ta có thể tính toán tương quan trong SQL không?

Question

Tính toán mối tương quan chéo giữa hai dãy số $X$ và $Y$ rất đơn giản. Giả sử rằng chúng ta muốn đo lường tác động của $X$ đối với $Y$, chúng ta chỉ cần dịch chuyển $Y$ về phía trước và tính toán mối tương quan giữa $X$ và phiên bản dịch chuyển của $Y$. Tất nhiên, người ta phải quyết định thay đổi bao nhiêu $Y$. Lý tưởng nhất là chúng tôi muốn đo lường tác động của $X$ đối với nhiều phiên bản thay đổi của $Y$. Thật vậy, chúng ta có thể muốn biết việc tăng $X$ ảnh hưởng như thế nào đến $Y$ trong một tuần, hai tuần, ba tuần, v.v.

Nội dung chính Show

Tương quan chéo cho hai biến
Các loại tương quan khác nhau
Nhiều $X$s và $Y$s
Biến tương quan trong SQL là gì?
Tên tương quan trong SQL là gì?
Dữ liệu tương quan được tính toán như thế nào?
Phương pháp nào là tốt nhất cho tương quan?

Tương quan chéo cho hai biến

Tôi sẽ sử dụng PostgreSQL 12. 3. Tôi đang sử dụng khá nhiều biểu thức bảng phổ biến để làm rõ các bước cần thực hiện. Trước tiên, hãy chọn một số dữ liệu giả

WITH dummy AS (
    SELECT * FROM (
    VALUES
        ('2020-01-01'::DATE, 1, 10),
        ('2020-01-02'::DATE, 2, 20),
        ('2020-01-03'::DATE, 3, 50),
        ('2020-01-04'::DATE, 0, -1000),
        ('2020-01-05'::DATE, -5, 0)
    ) AS dummy (date, x, y)
)

Như đã đề cập, chúng tôi muốn mã của mình hoạt động trong một số bước tùy ý về phía trước. Do đó, hãy xác định một loạt các giá trị delta sẽ được sử dụng để dịch chuyển

, dates AS (
    SELECT
        date AS present,
        delta,
        date + delta * '1 day'::interval AS future
    FROM dummy
    CROSS JOIN delta
)

0 về phía trước

, delta AS (
    SELECT *
    FROM generate_series(1, 3) delta
)

Bây giờ, hãy thêm số delta vào trường

, dates AS (
    SELECT
        date AS present,
        delta,
        date + delta * '1 day'::interval AS future
    FROM dummy
    CROSS JOIN delta
)

1 để xác định ngày trong tương lai

, dates AS (
    SELECT
        date AS present,
        delta,
        date + delta * '1 day'::interval AS future
    FROM dummy
    CROSS JOIN delta
)

>>> SELECT * FROM dates LIMIT 8;
present     delta   future
2020-01-01  1       2020-01-02
2020-01-02  1       2020-01-03
2020-01-03  1       2020-01-04
2020-01-04  1       2020-01-05
2020-01-05  1       2020-01-06
2020-01-01  2       2020-01-03
2020-01-02  2       2020-01-04
2020-01-03  2       2020-01-05

Bây giờ chúng ta có thể tham gia các ngày với các giá trị từ bảng ban đầu

, pairwise AS (
    SELECT
        dates.present,
        dates.future,
        dates.delta,
        present.x AS present_x,
        future.y AS future_y
    FROM
        dates,
        dummy AS present,
        dummy AS future
    WHERE dates.present = present.date
        AND dates.future = future.date
)

>>> SELECT * FROM pairwise ORDER BY present, delta;
present     future      delta   present_x   future_y
2020-01-01  2020-01-02  1       1           20
2020-01-01  2020-01-03  2       1           50
2020-01-01  2020-01-04  3       1           -1000
2020-01-02  2020-01-03  1       2           50
2020-01-02  2020-01-04  2       2           -1000
2020-01-02  2020-01-05  3       2           0
2020-01-03  2020-01-04  1       3           -1000
2020-01-03  2020-01-05  2       3           0
2020-01-04  2020-01-05  1       0           0

Cuối cùng, chúng ta chỉ cần tính toán mối tương quan trong mỗi nhóm. Các nhóm được xác định bởi trường

, dates AS (
    SELECT
        date AS present,
        delta,
        date + delta * '1 day'::interval AS future
    FROM dummy
    CROSS JOIN delta
)

2. Nói cách khác, chúng tôi sẽ tính toán mối tương quan cho tất cả các giá trị khớp với $(X_t, Y_{t+1})$, $(X_t, Y_{t+2})$ và $(X_t, Y_{t+3

, cross_corrs AS (
    SELECT
        delta,
        CORR(present_x, future_y) AS pearson
    FROM pairwise
    GROUP BY delta
    ORDER BY delta
)

, dates AS (
    SELECT
        date AS present,
        delta,
        date + delta * '1 day'::interval AS future
    FROM dummy
    CROSS JOIN delta
)

0

Vậy là xong, chúng ta đã hoàn thành. Bạn có thể nhấp vào nút chuyển đổi bên dưới nếu muốn sao chép/dán toàn bộ ví dụ

Nhấn vào đây để xem ví dụ đầy đủ

, dates AS (
    SELECT
        date AS present,
        delta,
        date + delta * '1 day'::interval AS future
    FROM dummy
    CROSS JOIN delta
)

1

Các loại tương quan khác nhau

Cho đến bây giờ, tôi đã đưa ra giả định phổ biến rằng "tương quan" đề cập đến tương quan Pearson. Có các loại tương quan khác, chẳng hạn như tương quan xếp hạng của Spearman. Cái sau không có sẵn trong PostgreSQL 12. 3, nhưng nó rất dễ tính toán, vì nó chỉ là tương quan Pearson của thứ hạng các giá trị của $X$ và $Y$. Trong SQL, điều này có nghĩa là

, dates AS (
    SELECT
        date AS present,
        delta,
        date + delta * '1 day'::interval AS future
    FROM dummy
    CROSS JOIN delta
)

2

, delta AS (
    SELECT *
    FROM generate_series(1, 3) delta
)

0

Lý tưởng nhất là chúng tôi muốn tính toán cả hai loại tương quan (Pearson và Spearman) cho từng giá trị delta. Điều này khá đơn giản để thực hiện, mặc dù hơi dài dòng

, delta AS (
    SELECT *
    FROM generate_series(1, 3) delta
)

1

, delta AS (
    SELECT *
    FROM generate_series(1, 3) delta
)

2

Nhiều $X$s và $Y$s

Chúng ta hiện có một khung để tính toán các loại tương quan chéo khác nhau giữa hai biến qua nhiều bước thời gian. Hãy đẩy phong bì và làm cho nó có thể tính toán các mối tương quan chéo cho nhiều biến. Nói một cách chính xác, chúng tôi muốn tính toán các mối tương quan chéo giữa một nhóm sáng kiến ($X$s) và a group of metrics ($Y$s).

Trước hết, hãy tạo thêm một số dữ liệu giả. Chúng tôi cũng sẽ tách $X$s khỏi $Y$s. Chúng tôi cũng sẽ làm tan chảy (hoặc "bỏ xoay vòng" như một số người muốn nói) cả hai nhóm. Lý do tại sao chúng tôi làm điều này là nó sẽ đơn giản hóa mã tiếp theo

, delta AS (
    SELECT *
    FROM generate_series(1, 3) delta
)

3

, delta AS (
    SELECT *
    FROM generate_series(1, 3) delta
)

4

Lưu ý rằng cách biểu diễn dữ liệu này phù hợp với các nguyên tắc Dữ liệu ngăn nắp do Hadley Wickam ủng hộ. Về cơ bản, làm phẳng/làm tan chảy một bảng giúp giảm bớt thao tác của bảng nói trên

Tiếp theo, chúng tôi sẽ tạo các ngày trong tương lai theo cách chính xác như chúng tôi đã làm trước đây

, delta AS (
    SELECT *
    FROM generate_series(1, 3) delta
)

5

Tuy nhiên, chúng tôi cần thay đổi định nghĩa của

, dates AS (
    SELECT
        date AS present,
        delta,
        date + delta * '1 day'::interval AS future
    FROM dummy
    CROSS JOIN delta
)

3 CTE. Nó khá đơn giản miễn là bạn đang tập trung tối thiểu. Đây đi

, delta AS (
    SELECT *
    FROM generate_series(1, 3) delta
)

6

, delta AS (
    SELECT *
    FROM generate_series(1, 3) delta
)

7

Bây giờ chúng ta có thể tính toán tất cả các mối tương quan mà chúng ta muốn bằng cách nhóm trên

, dates AS (
    SELECT
        date AS present,
        delta,
        date + delta * '1 day'::interval AS future
    FROM dummy
    CROSS JOIN delta
)

4. Một lần nữa, điều này vẫn tương đối đơn giản miễn là bạn tiến hành từng bước một

Biến tương quan trong SQL là gì?

Một thước đo thống kê cho biết mức độ liên quan chặt chẽ của hai bộ giá trị .

Tên tương quan trong SQL là gì?

Tên tương quan được liên kết với bảng, dạng xem, biểu thức bảng lồng nhau hoặc hàm bảng chỉ trong ngữ cảnh mà nó được xác định . Do đó, cùng một tên tương quan có thể được định nghĩa cho các mục đích khác nhau trong các phát biểu khác nhau. Trong biểu thức bảng lồng nhau hoặc hàm bảng, cần có tên tương quan.

Dữ liệu tương quan được tính toán như thế nào?

Hệ số tương quan được đo lường trên thang đo thay đổi từ + 1 đến 0 đến – 1 . Mối tương quan hoàn toàn giữa hai biến được thể hiện bằng + 1 hoặc -1. Khi một biến tăng khi biến kia tăng thì tương quan là dương; .