MySQL Rätsel - mitmachen und was dabei lernen

[webald]

zu #13b

Code: SQL  [Auswählen]

SELECT m.manufacturers_name, SUM(op.products_quantity) quantitysum,
SUM(op.products_price * op.products_quantity) gross
FROM orders o  
JOIN orders_products op ON op.orders_id = o.orders_id
JOIN products p ON p.products_id = op.products_id
JOIN manufacturers m ON m.manufacturers_id = p.manufacturers_id  
WHERE DATE(o.date_purchased) BETWEEN "2011-12-01" AND "2011-12-31"
GROUP BY m.manufacturers_id
HAVING o.order_status NOT 4

[thisisrob]

da fehlt jetzt noch ne neue Aufgabe

[piru]

[...]was ist daran falsch? 47 Millionen Ergebnisse will ich nicht sondern nur ca. 8000.

Code: SQL  [Auswählen]

SELECT `products`.`products_id`
FROM products, products_to_categories
WHERE
`products_to_categories`.`categories_id` <500

Dann die 2. Antwort auf dem Weg der 8000 Ergebnise:

Code: SQL  [Auswählen]

SELECT p.products_id
FROM products p
JOIN products_to_categories p2c
ON p.products_id = p2c.products_id
WHERE p2c.categories_id < 500

Gruß piru

[webald]

da fehlt jetzt noch ne neue Aufgabe

Na dann:

Gesucht sind die 10 teuersten Artikel je Hersteller

[Q]

Wie schon gepostet, komme ich gerade auf seltsame Ideen und da ist mir auch dieser alte Thread wieder eingefallen. Und da gerade einige Fragen über DB und Abfragen aufgekommen sind, dachte ich, ich könnte diesen hier wieder aus der Versenkung holen.

Hier auch direkt meine Lösungsansätze, den ich nicht optimal finde, weil er anscheinend sehr lange braucht und die manufaturer_id 10 überspringt  :

Code: SQL  [Auswählen]

SELECT p.products_model, p.products_price, p.manufacturers_id FROM products AS p
  LEFT JOIN products AS p2
      ON p.manufacturers_id = p2.manufacturers_id AND p.products_price <= p2.products_price
GROUP BY p.products_model
HAVING COUNT(*) <= 10
ORDER BY p.manufacturers_id, p.products_price DESC;

Zweiter Ansatz:

Code: SQL  [Auswählen]

SELECT p.products_model, p.products_price, p.manufacturers_id
FROM products p

  INNER JOIN
  (
    SELECT MAX(products_price) AS products_price, manufacturers_id
    FROM products p2
    GROUP BY manufacturers_id
    UNION
    SELECT MAX(p3.products_price) AS products_price, p3.manufacturers_id
    FROM products p3
    INNER JOIN (SELECT MAX(products_price) AS products_price, manufacturers_id
    FROM products p4
    GROUP BY manufacturers_id) p4 ON p3.products_price < p4.products_price
    AND p3.manufacturers_id = p4.manufacturers_id
    GROUP BY manufacturers_id
  ) p2 ON p.products_price = p2.products_price
  AND p.manufacturers_id = p2.manufacturers_id
ORDER BY
  manufacturers_id,
  products_price DESC,
  products_model;

Wobei hier das Problem ist, dass wenn es unter den mehrere Produkte mit dem selben Preis gibt, die in den Top 10 Preisen liegen, ich auch mehr als 10 Ergebnisse bekomme. Habe die Stelle um das auf 10 zu limitieren nicht gefunden. 

By the way, eine recht harte Nuss gewesen und so wirklich wissen, was ich da gemacht habe, tue ich auch nicht 

[vr]

Hallo oneQ

das ist eine schöne Aufgabe für window functions, wenn Du eine MariaDB ab 10.2 zur Verfügung hast:

Code: SQL  [Auswählen]

SELECT a.products_model, a.products_price, a.manufacturers_id, a.rang
FROM (
  SELECT products_model, products_price, manufacturers_id,
         dense_rank() OVER (partition BY manufacturers_id ORDER BY products_price DESC) rang
  FROM products) a
WHERE a.rang <= 10

Das statement liefert Dir uU mehr als 10 Treffer pro Hersteller, wenn zwei Artikel den gleichen Preis haben.

Erklärung: Mit window functions kannst Du inline gruppieren, sortieren und aggregieren. Gruppiert wird hier über partition by manufacturers_id, die Sortiervorschrift für die Rangfunktion dense_rank gibst Du mit order by products_price desc an. Eigentlich reicht das innere select schon, aber es sollen nur max die teuersten 10 pro Hersteller ausgegeben werden, die werden durch den wrapper ausgefiltert.

Die Performance ist gut.

Grüße, Volker

[Q]

Über "partition" bin ich bei der Suche nach der Lösung auch schon öfter gestolpert.
"rang" ist wieder ein Alias?

Mich würde ja der Lösungsansatz von webald interessieren, der hat schließlich die Aufgabe gestellt. 

Ansonsten scheint das hier wohl tot zu sein.

Vielleicht sollte man das dann nutzen um allgemein SQL-Abfragen-Fragen zu stellen!?!

[Q]

zu #13b

Code: SQL  [Auswählen]

SELECT m.manufacturers_name, SUM(op.products_quantity) quantitysum,
SUM(op.products_price * op.products_quantity) gross
FROM orders o  
JOIN orders_products op ON op.orders_id = o.orders_id
JOIN products p ON p.products_id = op.products_id
JOIN manufacturers m ON m.manufacturers_id = p.manufacturers_id  
WHERE DATE(o.date_purchased) BETWEEN "2011-12-01" AND "2011-12-31"
GROUP BY m.manufacturers_id
HAVING o.order_status NOT 4

Funktioniert bei mir irgendwie nicht.

Code: SQL  [Auswählen]

#1064 - Fehler IN der SQL-Syntax. Bitte die korrekte Syntax im Handbuch nachschlagen bei '0 LIMIT 0, 25' IN Zeile 9

Zeile 9 ist die letzte 

[vr]

Über "partition" bin ich bei der Suche nach der Lösung auch schon öfter gestolpert.
"rang" ist wieder ein Alias?

genau, nur zur Lesbarkeit der Ergebnistabelle, das AS kann man weglassen.

Mich würde ja der Lösungsansatz von webald interessieren, der hat schließlich die Aufgabe gestellt. 

Die letzte Zeile aus webalds Antwort ist auf zwei Arten falsch:

* HAVING ist sozusagen der where-part für die Aggregate (sum, max, count, group_concat, ..) einer Abfrage. Dort legst Du Bedingungen für die Aggregate fest. Aggregate sind hier die beiden sum-Ausdrücke, aber von denen wollen wir ja nix. Sowas wie sum(op.products_quantity) < 100 würde in den HAVING-part gehören. Die Bedingung "nicht storniert" gehört hier in den where part, denn sie bezieht sich auf die Basisdaten der Abfrage. Erst über die so eingeschränkte Menge wird dann aggregiert.

* ein Zahlenwert kann nicht durch einen logischen Operator wie NOT in sein Gegenteil umgewandelt werden. Das muss hier
 o.order_status <> 4 heißen. Das reicht auch, weil der Bestellstatus als not null definiert ist und deshalb nicht unbekannt sein kann.

Es muss heißen:

Code: SQL  [Auswählen]

SELECT m.manufacturers_name, SUM(op.products_quantity) quantitysum, SUM(op.products_price * op.products_quantity) gross
FROM orders o  
JOIN orders_products op ON op.orders_id = o.orders_id
JOIN products p ON p.products_id = op.products_id
JOIN manufacturers m ON m.manufacturers_id = p.manufacturers_id  
WHERE DATE(o.date_purchased) BETWEEN "2011-12-01" AND "2011-12-31"
AND o.orders_status <> 4
GROUP BY m.manufacturers_id

Ansonsten scheint das hier wohl tot zu sein.

Vielleicht sollte man das dann nutzen um allgemein SQL-Abfragen-Fragen zu stellen!?!

Das ist in diesem Thread so lebendig, wie Du/ihr wollt. Und klar, nutzt es, um Abfragenfragen zu stellen.

Grüße vr

[vr]

ok, neue challenge:

Zeige die Umsätze gruppiert nach Jahren und Monaten, wobei die Jahre vertikal (als reguläre Spalte) und die Monate horizontal (als separate Spalten) angeordnet sind, so dass auf Monatsebene sowohl ein Vergleich zum Vorjahr/Folgejahr als auch ein Vergleich zum Vormonat/Folgemonat möglich ist. In der Art

jahr | jan | feb | mrz | apr | mai | ....
==================================
...       |
2018 |
2019 |
2020 |

Grüße, vr

[Q]

Ist die Darstellung so ohne Nutzung von PHP möglich? Ich komme da irgendwie auf keinen grünen Zweig.

[vr]

Ja. In diesem Thread ist alles pur SQL. Eine Möglichkeit, Werte horizontal zu verteilen geht so:

Code: SQL  [Auswählen]

SELECT b.jahr,
SUM(IF(b.monat = 1, ums, NULL)) jan, SUM(IF(b.monat = 2, ums, NULL)) feb, SUM(IF(b.monat = 3, ums, NULL)) mrz,
SUM(IF(b.monat = 4, ums, NULL)) apr, SUM(IF(b.monat = 5, ums, NULL)) mai, SUM(IF(b.monat = 6, ums, NULL)) jun,
SUM(IF(b.monat = 7, ums, NULL)) jul, SUM(IF(b.monat = 8, ums, NULL)) aug, SUM(IF(b.monat = 9, ums, NULL)) sep,
SUM(IF(b.monat = 10, ums, NULL)) okt, SUM(IF(b.monat = 11, ums, NULL)) nov, SUM(IF(b.monat = 12, ums, NULL)) dez
FROM (...) b
GROUP BY 1

Das innere select (...), das die nötigen Daten ermittelt, hab ich gemeinerweise weggelassen

Grüße, Volker

[Q]

Nachdem ich erstmal die Abfrage für den Inhalt hatte, wusste ich nicht genau wie ich das dann entsprechend übertragen kann.
Aktueller Ansatz ist folgender

Code: SQL  [Auswählen]

SELECT jahr,
SUM(IF(monat = 1, ums, NULL)) jan, SUM(IF(monat = 2, ums, NULL)) feb, SUM(IF(monat = 3, ums, NULL)) mrz,
SUM(IF(monat = 4, ums, NULL)) apr, SUM(IF(monat = 5, ums, NULL)) mai, SUM(IF(monat = 6, ums, NULL)) jun,
SUM(IF(monat = 7, ums, NULL)) jul, SUM(IF(monat = 8, ums, NULL)) aug, SUM(IF(monat = 9, ums, NULL)) sep,
SUM(IF(monat = 10, ums, NULL)) okt, SUM(IF(monat = 11, ums, NULL)) nov, SUM(IF(monat = 12, ums, NULL)) dez

FROM (SELECT EXTRACT(MONTH FROM o.date_purchased) AS monat, EXTRACT(YEAR FROM o.date_purchased) AS jahr, format(SUM(ot.value),2) AS ums
FROM orders_total ot, orders o
WHERE o.orders_id=ot.orders_id
AND
class= "ot_subtotal"
GROUP BY 1,2  ) b
GROUP BY 1;

Jetzt weiß ich nur nicht wieso du 'b' für das Datum und 'ums' getrennt hast.
Bei meiner Lösung kommt mir das SUM doppelt gemoppelt vor, aber wenn ich das aus dem oberen SELECT raus nehme, klappt es nicht mehr 

[Q]

etwas angepasst. Die SUM ist jetzt nicht mehr doppelt und die Werte passen auch besser. Jetzt auch mit dem Gesamtumsatz inkl. VK und USt. . Vorher hatte ich mit dem Warenwert gerechnet (den ich selber interessanter finde).

Code: SQL  [Auswählen]

SELECT jahr,
SUM(IF(monat = 1, ums, NULL)) jan, SUM(IF(monat = 2, ums, NULL)) feb, SUM(IF(monat = 3, ums, NULL)) mrz,
SUM(IF(monat = 4, ums, NULL)) apr, SUM(IF(monat = 5, ums, NULL)) mai, SUM(IF(monat = 6, ums, NULL)) jun,
SUM(IF(monat = 7, ums, NULL)) jul, SUM(IF(monat = 8, ums, NULL)) aug, SUM(IF(monat = 9, ums, NULL)) sep,
SUM(IF(monat = 10, ums, NULL)) okt, SUM(IF(monat = 11, ums, NULL)) nov, SUM(IF(monat = 12, ums, NULL)) dez

FROM (
SELECT EXTRACT(MONTH FROM o.date_purchased) AS monat,
       EXTRACT(YEAR FROM o.date_purchased) AS jahr,
       ot.value AS ums
       FROM orders_total ot, orders o
       WHERE o.orders_id = ot.orders_id
       AND class = 'ot_total'
) b
GROUP BY 1;

Das b. ist komplett raus.

[Schreinermeister]

Hallo zusammen,
ich möchte ein Rätsel "aufgeben".

Ich erhalte von verschiedenen Großhändlern täglich eine csv Liste mit den Sendungsverfolgungsnummern.
Leider werden einige Lieferungen dabei mehrfach aufgeführt. (auch tagesübergreifend)
Ich habe diese doppelten Werte bisher mit einem Tabellenkalkulaionsprogramm herausgefiltert und die verbleibenden Datensätze mit enem automatisiert erstellten SQL_Befehl z.B.

Code: SQL  [Auswählen]

INSERT INTO `orders_tracking`(`tracking_id`, `orders_id`, `carrier_id`, `parcel_id`, `date_added`) VALUES('','65729','2','01785085852541',now());
 

in die DB übertragen.
Nun wäre es einfacher, den Wert nur in die DB einzutragen, wenn dieser nicht bereits vorhanden ist.
Durch die vorherige Abfrage würe das lästige Filtern der Listen entfallen.

Meine Internetrecherche war bisher nicht zielführend oder gar verwirrend.  
Kann mir einer der "wissenden" einen Tipp geben.

Gruß Chris