Untersuchen von Daten von SAP® Business Data Cloud¶

Durchsuchen der Datenbank, Schemata und Tabellen¶

Untersuchen Sie die Datenbank:

DESC DATABASE CUSTOMER;

Copy

Das sollte zu folgenden Ergebnissen führen:

+--------------------------------+---------------------------------+
|created_on                              | name                            | kind      |
+--------------------------------+---------------------------------+
| 2025-12-17 13:30:01.062 -0800  | INFORMATION_SCHEMA      | SCHEMA    |
| 2025-12-17 13:11:12.206 -0800  | customer            | SCHEMA    |
+--------------------------------+---------------------------------+

Dabei steht jede Zeile für das Schema Ihrer Datenbank.

Prüfen Sie die Tabellen in der Datenbank:

SHOW TABLES IN CUSTOMER;

Copy

Das sollte zu folgenden Ergebnissen führen:

+------------------------+----------------+-------------+-------+-------+--------+
| name                               | database_name  | schema_name | kind  | rows  | bytes  |
+------------------------+----------------+-------------+-------+-------+--------+
| customer                           | CUSTOMER       | customer    | TABLE | 2174  | 215708 |
| customercompanycode          | CUSTOMER               | customer    | TABLE | 1792  | 37311  |
| customerdunning                | CUSTOMER             | customer    | TABLE | 44    | 4912   |
| customersalesarea            | CUSTOMER               | customer    | TABLE | 442   | 34415  |
| customersalesareatax   | CUSTOMER             | customer    | TABLE | 883   | 9153   |
| customerunloadingpoint | CUSTOMER             | customer    | TABLE | 37    | 13253  |
+------------------------+----------------+-------------+-------+-------+--------+

Abfragen von Tabellen in der CUSTOMER-Datenbank¶

Abfrage der Tabelle „customer“:

SELECT * FROM CUSTOMER.customer.customer;

SELECT * FROM CUSTOMER.customer.customer WHERE CREATEDBYUSER = 'KAPOORM'

Copy

Abgeleitete Daten aus freigegebenen Daten erstellen¶

Erstellen von L1-Daten durch Verknüpfen von Tabellen aus 2 freigegebenen Datenprodukten:

-- Join tables in CUSTOMER and ENTRYVIEWJOURNALENTRY to find top 10 customers by revenue
SELECT
    c.customer,
    c.customername,
    c.country,
    c.region,
    c.businesstype,
    COUNT(DISTINCT e.accountingdocument) as num_transactions,
    SUM(e.amountincompanycodecurrency) as total_revenue,
    AVG(e.amountincompanycodecurrency) as avg_transaction_amount
FROM CUSTOMER.customer.customer c
JOIN ENTRYVIEWJOURNALENTRY.entryviewjournalentry.operationalacctgdocitem e
   ON c.customer = e.customer
WHERE c.deletionindicator = FALSE
GROUP BY 1,2,3,4,5
ORDER BY total_revenue DESC
LIMIT 10;

Tabelle als Auswahl erstellen (CTAS) in einer neuen Datenbank¶

Erstellen Sie eine neue Datenbank, die die CTAS enthalten soll:

CREATE DATABASE CUSTOMER_CTAS_DEMO;
USE DATABASE CUSTOMER_CTAS_DEMO;

-- Create the CTAS
CREATE OR REPLACE TABLE top_customers_by_revenue AS
SELECT
  c.customer,
  c.customername,
  c.country,
  c.region,
  c.businesstype,
  COUNT(DISTINCT e.accountingdocument) as num_transactions,
  SUM(e.amountincompanycodecurrency) as total_revenue,
  AVG(e.amountincompanycodecurrency) as avg_transaction_amount
FROM CUSTOMER.customer.customer c
JOIN ENTRYVIEWJOURNALENTRY.entryviewjournalentry.operationalacctgdocitem e
    ON c.customer = e.customer
WHERE c.deletionindicator = FALSE
 GROUP BY 1,2,3,4,5
 ORDER BY total_revenue DESC;

 -- Query the CTAS
 SELECT * FROM top_customers_by_revenue LIMIT 10;