Saya memiliki tabel hasil yang mirip dengan di bawah ini:

Bagaimana cara saya menanyakan ticker yang meningkat pada harga terbaru dan sentimen_perubahan dari waktu ke waktu. Misalnya Anda dapat melihat AMSC telah meningkat baik pada harga_terbaru dan perubahan_sentimen. Saya menggunakan postgresql terima kasih atas bantuan Anda

"created_at"    "ticker"    "title" "latest_price"  "sentiment_change"
"2020-05-24 06:41:40.948862"    "AMSC"  "American Superconductor Corporation"   "6.66"  "5.56"
"2020-05-24 06:42:02.987018"    "AMSC"  "American Superconductor Corporation"   "7.66"  "6.56"
"2020-05-24 07:59:08.014871"    "AMSC"  "American Superconductor Corporation"   "8.66"  "7.27"
"2020-05-24 08:00:23.406509"    "AMSC"  "American Superconductor Corporation"   "9.66"  "8.27"
"2020-05-24 08:04:02.881144"    "AMSC"  "American Superconductor Corporation"   "10.66" "9.27"
"2020-05-24 06:41:51.797114"    "AUPH"  "Aurinia Pharmaceuticals Inc."  "16.24" "-0.37"
"2020-05-24 06:41:52.768141"    "CGC"   "Canopy Growth Corporation "    "19.42" "-0.89"
"2020-05-24 06:42:06.947722"    "COF"   "Capital One Financial Corp."   "60.21" "9.19"
"2020-05-24 07:58:49.696729"    "CNC"   "Centene Corp." "64.84" "0.0"
"2020-05-24 08:00:05.909404"    "CNC"   "Centene Corp." "64.84" "0.0"
1
STEIN 24 Mei 2020, 10:22

1 menjawab

Jawaban Terbaik

Saya membuat db-fiddle untuk ini di sini: https://www.db-fiddle. com/f/37qeZWoFpVLkJLqN16TirX/0

Jika versi PostgreSQL Anda mendukung LAG, Anda dapat menggunakan kueri di bawah ini untuk memulai. Saya telah melakukan bagian yang meningkat, Anda dapat melakukan bagian yang menurun.

Anda kemudian dapat, misalnya, menghitung berapa kali latest_price atau sentiment_change bertambah/berkurang sehubungan dengan jumlah pengamatan yang Anda miliki; dan jika di atas/di bawah ambang batas tertentu, tentukan tindakan Anda.

SELECT
    created_at, 
    ticker,
    latest_price, 
    sentiment_change, 
    LAG(latest_price,1) OVER (
        PARTITION BY ticker 
        ORDER BY created_at) AS previous_price,
    CASE WHEN latest_price >= LAG(latest_price, 1) OVER (
        PARTITION BY ticker 
        ORDER BY created_at)
         THEN 1 ELSE 0 END AS increased_price_flag,
    LAG(sentiment_change,1) OVER (
        PARTITION BY ticker 
        ORDER BY created_at) AS previous_sentiment_change,
    CASE WHEN sentiment_change >= LAG(sentiment_change, 1) OVER (
        PARTITION BY ticker 
        ORDER BY created_at)
         THEN 1 ELSE 0 END AS increased_sentiment_change_flag
FROM results
ORDER BY ticker, created_at;

Hasil:

| created_at               | ticker | latest_price | sentiment_change | previous_price | increased_price_flag | previous_sentiment_change | increased_sentiment_change_flag |
| ------------------------ | ------ | ------------ | ---------------- | -------------- | -------------------- | ------------------------- | ------------------------------- |
| 2020-05-24T06:41:40.948Z | AMSC   | 6.66         | 5.56             |                | 0                    |                           | 0                               |
| 2020-05-24T06:42:02.987Z | AMSC   | 7.66         | 6.56             | 6.66           | 1                    | 5.56                      | 1                               |
| 2020-05-24T07:59:08.014Z | AMSC   | 8.66         | 7.27             | 7.66           | 1                    | 6.56                      | 1                               |
| 2020-05-24T08:00:23.406Z | AMSC   | 9.66         | 8.27             | 8.66           | 1                    | 7.27                      | 1                               |
| 2020-05-24T08:04:02.881Z | AMSC   | 10.66        | 9.27             | 9.66           | 1                    | 8.27                      | 1                               |
| 2020-05-24T06:41:51.797Z | AUPH   | 16.24        | -0.37            |                | 0                    |                           | 0                               |
| 2020-05-24T06:41:52.768Z | CGC    | 19.42        | -0.89            |                | 0                    |                           | 0                               |
| 2020-05-24T07:58:49.696Z | CNC    | 64.84        | 0.00             |                | 0                    |                           | 0                               |
| 2020-05-24T08:00:05.909Z | CNC    | 64.84        | 0.00             | 64.84          | 1                    | 0.00                      | 1                               |
| 2020-05-24T06:42:06.947Z | COF    | 60.21        | 9.19             |                | 0                    |                           | 0                               |
2
mechanical_meat 24 Mei 2020, 08:26