选择国家的帐户余额最高

Question

我是SQL的新手。我正在尝试选择每个日期>具有最高帐户余额的国家。。我正在加入poade_transactions表（以获取基于Customer_ID的金额），并与客户表加入以获取国家

schema（MySQL v8.0）

CREATE TABLE deposit_transactions (
  `deposit_id` VARCHAR(7),
  `customer_id` VARCHAR(5),
  `date` TEXT,
  `transaction_type` VARCHAR(7),
  `amount` INTEGER,
  `currency` VARCHAR(3)
);

INSERT INTO deposit_transactions
  (`deposit_id`, `customer_id`, `date`, `transaction_type`, `amount`, `currency`)
VALUES
  ('DE47653', 'DO900', '1/1/2019', 'pay_in', '10000', 'EUR'),
  ('DE47654', 'DO901', '2/1/2019', 'pay_in', '10000', 'EUR'),
  ('DE47655', 'DO902', '2/1/2019', 'pay_in', '10000', 'EUR'),
  ('UK47656', 'DO903', '3/1/2019', 'pay_in', '10000', 'GBP'),
  ('UK47657', 'DO904', '3/1/2019', 'pay_in', '130000', 'GBP'),
  ('DE47658', 'DO905', '3/1/2019', 'pay_in', '140000', 'EUR'),
  ('DE47659', 'DO906', '3/1/2019', 'pay_in', '10000', 'EUR'),
  ('DE47660', 'DO907', '3/1/2019', 'pay_in', '10000', 'EUR'),
  ('DE47661', 'DO908', '3/1/2019', 'pay_in', '100000', 'EUR'),
  ('DE47662', 'DO909', '4/1/2019', 'pay_in', '10000', 'EUR'),
  ('UK47663', 'DO910', '4/1/2019', 'pay_in', '10000', 'GBP'),
  ('UK47664', 'DO911', '4/1/2019', 'pay_in', '5000', 'GBP'),
  ('UK47665', 'DO912', '4/1/2019', 'pay_in', '4000', 'GBP'),
  ('DE47666', 'DO913', '5/1/2019', 'pay_in', '10000', 'EUR'),
  ('DE47661', 'DO908', '5/1/2019', 'pay_out', '50000', 'EUR'),
  ('DE47667', 'DO914', '11/1/2019', 'pay_in', '30000', 'EUR'),
  ('DE47668', 'DO915', '11/1/2019', 'pay_in', '10000', 'EUR'),
  ('DE47669', 'DO916', '11/1/2019', 'pay_in', '25000', 'EUR'),
  ('DE47670', 'DO917', '11/1/2019', 'pay_in', '50000', 'EUR'),
  ('DE47667', 'DO914', '12/1/2019', 'pay_out', '7000', 'EUR'),
  ('DE47667', 'DO914', '12/15/2019', 'pay_in', '12000', 'EUR'),
  ('DE47671', 'DO918', '1/1/2021', 'pay_in', '9000', 'EUR'),
  ('DE47672', 'DO919', '1/1/2021', 'pay_in', '10000', 'EUR'),
  ('DE47673', 'DO920', '1/1/2021', 'pay_in', '11000', 'EUR'),
  ('DE47674', 'DO921', '1/1/2021', 'pay_in', '12000', 'EUR'),
  ('DE47675', 'DO922', '1/1/2021', 'pay_in', '13000', 'EUR'),
  ('DE47676', 'DO923', '1/1/2021', 'pay_in', '14000', 'EUR'),
  ('DE47677', 'DO924', '1/1/2021', 'pay_in', '30000', 'EUR'),
  ('DE47678', 'DO925', '1/1/2021', 'pay_in', '16000', 'EUR');

CREATE TABLE customers (
  `customer_id` VARCHAR(5),
  `country` VARCHAR(7)
);

INSERT INTO customers
  (`customer_id`, `country`)
VALUES
  ('DO900', 'Germany'),
  ('DO901', 'Germany'),
  ('DO902', 'Spain'),
  ('DO903', 'UK'),
  ('DO904', 'UK'),
  ('DO905', 'Austria'),
  ('DO906', 'Germany'),
  ('DO907', 'Germany'),
  ('DO908', 'Germany'),
  ('DO909', 'Germany'),
  ('DO910', 'UK'),
  ('DO911', 'UK'),
  ('DO912', 'UK'),
  ('DO913', 'Germany'),
  ('DO914', 'Austria'),
  ('DO915', 'Germany'),
  ('DO916', 'Austria'),
  ('DO917', 'Germany'),
  ('DO918', 'Germany'),
  ('DO919', 'Spain'),
  ('DO920', 'Germany'),
  ('DO921', 'Spain'),
  ('DO922', 'Germany'),
  ('DO923', 'Germany'),
  ('DO924', 'Germany'),
  ('DO925', 'Spain'),
  ('DO926', 'Germany'),
  ('DO927', 'Germany'),
  ('DO928', 'Germany'),
  ('DO929', 'Germany'),
  ('DO711', 'UK'),
  ('DO712', 'UK'),
  ('DO713', 'Germany'),
  ('DO714', 'Austria'),
  ('DO715', 'Germany'),
  ('DO716', 'Austria'),
  ('DO717', 'Germany'),
  ('DO718', 'Germany'),
  ('DO719', 'Spain'),
  ('DO720', 'Germany'),
  ('DO721', 'Spain'),
  ('DO722', 'Germany'),
  ('DO723', 'Germany'),
  ('DO724', 'Germany'),
  ('DO725', 'Spain'),
  ('DO726', 'Germany'),
  ('DO727', 'Germany'),
  ('DO728', 'Germany'),
  ('DO729', 'Germany');

---

以下是我创建的示例查询。但是我不确定它是否正确

查询

SELECT t.customer_id,
       c.country,t.total_amount
FROM  (SELECT customer_id,
              date,
              transaction_type,
              Sum(amount)
                OVER (
                  partition BY customer_id ) AS total_amount_transfered,
              Row_number()
                OVER (
                  partition BY customer_id
                  ORDER BY date DESC)        AS n
       FROM   deposit_transactions)t
      INNER JOIN customers AS c
              ON c.customer_id = t.customer_id
WHERE  t.n = 1
       AND t.transaction_type = 'pay_in' order by total_amount desc limit 1;

---

Answer 1

步骤1 。找到每个国家的总余额。

由于您需要达到余额，并且有两种交易，因此我宁愿将“ 付款”交易转换为负余额，然后离开” 付款 “交易原样。另外，您应该考虑到不同的货币，因为“ gbp ”货币的价值高于“ eur ”货币（GBP〜UR*0.85）。如果您不想考虑“ 付款”的负面影响，则可以将它们转换为0。此余额在这里由a 案例 语句。

然后，余额在相应国家/地区的sum函数（由于join操作）上访问，并分组为“ country ”和“ date ”字段。

SELECT country,
       date AS transaction_date,
--     STR_TO_DATE(date, '%m/%d/%Y') AS transaction_date,
       SUM(CASE WHEN transaction_type = 'pay_in' AND currency = 'EUR' THEN amount 
                WHEN transaction_type = 'pay_in' AND currency = 'GBP' THEN amount/0.85
--              WHEN transaction_type = 'pay_out'                      THEN 0
                WHEN transaction_type = 'pay_out' AND currency = 'EUR' THEN -amount
                WHEN transaction_type = 'pay_out' AND currency = 'GBP' THEN -amount/0.85 
           END) AS amount_eur
FROM       deposit_transactions dt
INNER JOIN customers c
        ON dt.customer_id = c.customer_id
GROUP BY country,
         transaction_date

在此步骤中，您还可以使用text键入date使用str_to_date < /代码>功能。

---

步骤2 。在总额中对国家进行排名。

为了应用等级，您可以使用 row_number 窗口函数，以下降的余额分区和订单分区。

SELECT *, ROW_NUMBER() OVER(
              PARTITION BY transaction_date
              ORDER     BY amount_eur DESC ) AS rn
FROM   total_amounts_per_country

---

步骤3 。选择拥有最高余额的国家。

鉴于每个日期将有1个，您只能所有具有等于1的行的行。然后选择有趣的字段，即“ transaction_date ”和“ country ”。

SELECT transaction_date, 
       country
FROM ranked_countries
WHERE rn = 1

---

完整查询。它使用mysql 常见的表表达式结合三个步骤。

WITH total_amounts_per_country AS(
    SELECT country,
           date AS transaction_date,
--         STR_TO_DATE(date, '%m/%d/%Y') AS transaction_date,
           SUM(CASE WHEN transaction_type = 'pay_in' AND currency = 'EUR' THEN amount 
                    WHEN transaction_type = 'pay_in' AND currency = 'GBP' THEN amount/0.85
--                  WHEN transaction_type = 'pay_out'                      THEN 0
                    WHEN transaction_type = 'pay_out' AND currency = 'EUR' THEN -amount
                    WHEN transaction_type = 'pay_out' AND currency = 'GBP' THEN -amount/0.85               END) AS amount_eur
    FROM       deposit_transactions dt
    INNER JOIN customers c
            ON dt.customer_id = c.customer_id
    GROUP BY country,
             transaction_date
), ranked_countries AS (
    SELECT *, ROW_NUMBER() OVER(
                  PARTITION BY transaction_date
                  ORDER     BY amount_eur DESC ) AS rn
    FROM   total_amounts_per_country
)
SELECT transaction_date, 
       country
FROM ranked_countries
WHERE rn = 1

检查演示在这里。