SQL SQL: Komplexes "Update Select"-Statement mit mehreren Tabellen

CyborgBeta · 23. April 2025

Hm, ich habe noch eine Frage. Habe jetzt zusätzlich noch eine Spalte mit dem Kursverlauf der Top100. Wie kann ich diesen zusätzlichen Verlauf am besten mit in den Chart "hineinrechnen" als Vergleich bzw. Referenz? Welche Ansätze gibt es dafür? (Auf Mathe-Ebene?)

Bisher habe ich das über den Durchschnitt gemacht:

SQL:

CREATE VIEW book1_v2_24h AS
WITH
  base AS (
SELECT
    a.*,
    a.usd - (
    SELECT
        COALESCE(SUM(usd), 0)
    FROM
        book1
    WHERE
        typ = 'd'
        AND NOT is_old
        AND datum <= a.datum
        AND datum > DATE_SUB(CURDATE(), INTERVAL 1 DAY)
      ) + (
    SELECT
        COALESCE(SUM(usd), 0)
    FROM
        book1
    WHERE
        typ = 'w'
        AND NOT is_old
        AND datum <= a.datum
        AND datum > DATE_SUB(CURDATE(), INTERVAL 1 DAY)
      ) AS new_usd
FROM
    book1 a
WHERE
    a.typ = "b"
    AND NOT a.is_old
    AND a.datum > DATE_SUB(CURDATE(), INTERVAL 1 DAY)
  )
SELECT
    base.*,
    base.new_usd / 1.1 AS new_eur,
    base.cmc_100 * (
    SELECT
        SUM(base.new_usd) / SUM(base.cmc_100)
    FROM
        base
  ) AS new_cmc
FROM
    base
ORDER BY
    datum ASC;

Ich berechne also erste die Summe von usd, teile durch die Summe von cmc_100, und multipliziere dann cmc_100 damit.

Das Ergebnis sieht visuell ungefähr so aus:

Wie man sehen kann, entsteht dadurch (immer) ein Schnittpunkt der Graphen ungefähr in der Mitte ... Ich glaube, das ist noch nicht das Gelbe vom Ei.

CyborgBeta · 23. April 2025

Ach so, so kompliziert muss man es gar nicht machen ... einfach jeweils den ersten Wert wählen:

SQL:

ALTER VIEW book1_v2_24h AS
WITH
  base AS (
SELECT
    a.*,
    a.usd - (
    SELECT
        COALESCE(SUM(usd), 0)
    FROM
        book1
    WHERE
        typ = 'd'
        AND NOT is_old
        AND datum <= a.datum
        AND datum > DATE_SUB(CURDATE(), INTERVAL 1 DAY)
      ) + (
    SELECT
        COALESCE(SUM(usd), 0)
    FROM
        book1
    WHERE
        typ = 'w'
        AND NOT is_old
        AND datum <= a.datum
        AND datum > DATE_SUB(CURDATE(), INTERVAL 1 DAY)
      ) AS new_usd
FROM
    book1 a
WHERE
    a.typ = "b"
    AND NOT a.is_old
    AND a.datum > DATE_SUB(CURDATE(), INTERVAL 1 DAY)
  )
SELECT
    base.*,
    base.new_usd / 1.1 AS new_eur,
    base.cmc_100 * (FIRST_VALUE(base.new_usd) OVER (ORDER BY base.datum) / FIRST_VALUE(base.cmc_100) OVER (ORDER BY base.datum)) AS new_cmc
FROM
    base
ORDER BY
    datum ASC;

CyborgBeta · 24. April 2025

Kann mir jemand sagen, weshalb das nicht funktioniert?

Ich dachte mir, mit parametrisierten Funktionen und virtuellen Spalten sei das besser:

SQL:

DELIMITER //

   CREATE FUNCTION CalcNewUSD(p_id INT, p_days INT)
   RETURNS DECIMAL(10,2)
   NOT DETERMINISTIC
   BEGIN
     DECLARE sum_d DECIMAL(10,2);
     DECLARE sum_w DECIMAL(10,2);
     DECLARE datum_1, datum_2 DATETIME;
     SELECT datum INTO datum_1 FROM book1 WHERE id = p_id LIMIT 1;
     SET datum_2 = DATE_SUB(CURDATE(), INTERVAL p_days DAY);
     SELECT COALESCE(SUM(usd),0)
       INTO sum_d
       FROM book1
      WHERE typ = 'd'
        AND NOT is_old
        AND datum <= datum_1
        AND datum >= datum_2;
     SELECT COALESCE(SUM(usd),0)
       INTO sum_w
       FROM book1
      WHERE typ = 'w'
        AND NOT is_old
        AND datum <= datum_1
        AND datum >= datum_2;
     RETURN (
         (SELECT usd FROM book1 WHERE id = p_id LIMIT 1)
         - sum_d
         + sum_w
       );
   END;

   //

DELIMITER ;

Fehlermeldung:

Code:

SQL-Fehler [1064] [42000]: (conn=267) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '//' at line 1

Fehlerposition:

Version:

Code:

in_predicate_conversion_threshold    1000
protocol_version    10
slave_type_conversions   
system_versioning_alter_history    ERROR
system_versioning_asof    DEFAULT
system_versioning_insert_history    OFF
tls_version    TLSv1.2,TLSv1.3
version    11.4.5-MariaDB-ubu2404
version_comment    mariadb.org binary distribution
version_compile_machine    x86_64
version_compile_os    debian-linux-gnu
version_malloc_library    system

Dass es nicht deterministisch sein kann, habe ich schon herausgefunden, da "current date" verwendet wird (und Einstein sagt ja, Zeit sei relativ).

Ergänzung (24. April 2025)

Ufff, ok, es lag an DBaever (light) ...

Habe mich direkt mit der Datenbank verbunden:

Code:

Database changed
MariaDB [db]> DELIMITER //
MariaDB [db]>    CREATE FUNCTION CalcNewUSD(p_id INT, p_days INT)
    ->    RETURNS DECIMAL(10,2)
    ->    DETERMINISTIC
    ->    BEGIN
    ->      DECLARE sum_d DECIMAL(10,2);
    ->      DECLARE sum_w DECIMAL(10,2);
    ->      DECLARE datum_1, datum_2 DATETIME;
    ->      SELECT datum INTO datum_1 FROM book1 WHERE id = p_id LIMIT 1;
    ->      SET datum_2 = DATE_SUB(CURDATE(), INTERVAL p_days DAY);
    ->      SELECT COALESCE(SUM(usd),0)
    ->        INTO sum_d
    ->        FROM book1
    ->       WHERE typ = 'd'
    ->         AND NOT is_old
    ->         AND datum <= datum_1
    ->         AND datum >= datum_2;
    ->      SELECT COALESCE(SUM(usd),0)
    ->        INTO sum_w
    ->        FROM book1
    ->       WHERE typ = 'w'
    ->         AND NOT is_old
    ->         AND datum <= datum_1
    ->         AND datum >= datum_2;
    ->      RETURN (
    ->          (SELECT usd FROM book1 WHERE id = p_id LIMIT 1)
    ->          - sum_d
    ->          + sum_w
    ->        );
    ->    END;
    ->
    -> //
Query OK, 0 rows affected (0.008 sec)

MariaDB [db]> DELIMITER ;
MariaDB [db]> select CalcNewUSD(161,1);
+-------------------+
| CalcNewUSD(161,1) |
+-------------------+
|            696.52 |
+-------------------+
1 row in set (0.002 sec)

Deterministisch ist es auch.

Jetzt noch eine generated virtual column daraus machen.

Ergänzung (24. April 2025)

Hm, das kann ich leider vergessen: https://stackoverflow.com/a/47053123

, and user-defined functions are not permitted.

CyborgBeta · 28. April 2025

Ich wärme noch mal auf.

Die Frage ist glaube ich, wie man den Einstiegspunkt des zweiten ("Referenz")-Kursverlauf herausfinden kann.

Bisher nehme ich einfach first(usd)=first(cmc).

Jetzt habe ich das geändert:

SQL:

CREATE FUNCTION `CalcNewCMC`(p_id int, p_days int) RETURNS decimal(12,2)
    DETERMINISTIC
BEGIN
    DECLARE datum_1, datum_2 DATETIME;
    DECLARE first_usd, first_cmc decimal(12,2);
    
    SELECT datum INTO datum_1 FROM book1 WHERE id = p_id LIMIT 1;
    # Go one day from interval start back into the past and calculate the cmc_100 average up to the start of the interval:
    SET datum_1 = DATE_SUB(current_timestamp(), INTERVAL (p_days + 1) DAY);
    SET datum_2 = DATE_SUB(current_timestamp(), INTERVAL p_days DAY);
    
    SELECT usd into first_usd from book1 where typ = 'b'
    and not is_old
    and datum >= datum_2
    order by datum ASC
    limit 1;
    
    SELECT AVG(cmc_100) into first_cmc from book1 where typ = 'b'
    and not is_old
    and datum >= datum_1
    and datum <= datum_2
    order by datum ASC;
    
    RETURN (
         (SELECT cmc_100 FROM book1 WHERE id = p_id LIMIT 1)
         * first_usd
         / first_cmc
       );
END

(habe mich wirklich bemüht, das einigermaßen leserlich zu formatieren ...)

Ich berechne also den durchschnittlichen CMC vom Vortag des Starts des Zeitintervalls ...

Ehm, ein Zeitstrahl (wie in der Schule) könnte für 3 Days also so aussehen:

Frage: Ergibt das Sinn?

Suche

SQL SQL: Komplexes "Update Select"-Statement mit mehreren Tabellen

CyborgBeta

Banned

CyborgBeta

Banned

CyborgBeta

Banned

CyborgBeta

Banned

Ähnliche Themen