יצירת עמודה ממגוון שדות

ברוכים הבאים לתרגיל העשירי!

בתרגיל הבא נתרגל לקיחה של ערכים מעמודות לפי תנאי. זה תאור קצת כללי לתרגיל (עם 2 השאלות), אבל זה בגדול מה שהיה לי בראש כשניסיתי לתאר את האתגרים שאני מנסה לשתף פה.

שאלה 1

הדאטה

הגעתם למקום שעוד לא יצר תהליכי ETL מוסדרים ולכן אתם בעיקר עובדים עם דאטה מ-OLTP.

למטה תוכלו לראות דיאגרמת ישויות קשרים של הטבלאות ואת השם של כל אחת מעליה (עם מפתח מחבר של Payment_id). בגדול יש לנו טבלת משלמים ואילו המקבלים השונים וגובה הסכום פזורים בטבלאות שונות.

צרו פלט שיציג את היוזר המשלם, היוזר המקבל וכמה הוא קיבל סה”כ מאותו משלם, ללא קשר לסוג התשלום. שימו לב כי ישנם תשלומים בלי מידע על סוג התשלום (התעלמו מהם)

תרגיל 2

פה כבר יש לנו את טבלת התשלומים המלאה:

SELECT  TOP 5 *
FROM q10_payments

5 records

payer	receiver	payment_id	Transaction_date	Amount
14	7	1	2020-06-30	45
14	7	2	2020-07-28	17
14	7	3	2020-07-29	24
1	7	4	2020-08-27	4
1	7	5	2020-08-28	89

אנחנו מעוניינים ליצור טבלת מידע על כל המשלמים ומקבלי התשלום.

לשם כך, הדפיסו טבלה שתציג לנו עמודת יוזר מקור, עמודת יוזר רפרנס, כמה תשלומים היוזר רפרנס קיבל מהמקור, כמה תשלומים היוזר רפרנס שלח למקור ומה היה התאריך האחרון שנשלח ביניהם תשלום (לא משנה הכיוון). יוזר רפרנס הוא כל יוזר שהיה לו קשר כלשהו - כמשלם או מקבל - ליוזר המקור שלנו. אנחנו רוצים שכל משתמש ייחודי בטבלה (משלם/מקבל) יופיע כיוזר מקור

את הפלט סדרו לפי מספר הקשרים השונים של כל יוזר בסדר יורד. כלומר, היוזר שהופיע בהכי הרבה קשרים יהיה ראשון, אחריו היוזר עם מספר המופעים הגדול אחריו וכן הלאה.

נסו בעצמכם

אונליין

נסו בחלון טקסט למטה (SQLite):

/* For Q1:
-- SELECT * FROM Q10_PAYERS
-- SELECT * FROM Cash
-- SELECT * FROM Bit
-- SELECT * FROM Checks
*/

-- Q2:
-- SELECT * FROM q10_payments

נסו על המחשב שלכם

הצג קוד ליצירת טבלה

CREATE TABLE q10_payers (
    payment_id  INT,
    payer   INT
);

INSERT INTO q10_payers (payment_id, payer ) VALUES
    ('1', '14 '),
    ('2', '14 '),
    ('3', '14 '),
    ('4', '1 '),
    ('5', '1 '),
    ('6', '6 '),
    ('7', '6 '),
    ('8', '7 '),
    ('9', '7 '),
    ('10', '3 '),
    ('11', '3 '),
    ('12', '3 '),
    ('13', '3 '),
    ('14', '7 '),
    ('15', '7 '),
    ('16', '7 '),
    ('17', '4 '),
    ('18', '12 '),
    ('19', '12 '),
    ('20', '12 ');

    CREATE TABLE Cash (
    receiver    INT,
    payment_id  INT,
    Amount  INT
);

INSERT INTO Cash (payment_id, receiver,  Amount ) VALUES
    ('1', '7', '45 '),
    ('4', '1', '4 '),
    ('13','3',  '84 '),
    ('18','7',  '94 '),
    ('19','7',  '25 '),
    ('20','7',  '80 ');


    CREATE TABLE Bit (
    receiver    INT,
    payment_id  INT,
    amount  INT
);

INSERT INTO Bit (payment_id, receiver, amount ) VALUES
    ('2', '7', '17 '),
    ('5', '7', '89 '),
    ('10','1',  '31 '),
    ('14','22', '23 '),
    ('16','22', '63 '),
    ('17','7',  '75 ');

    CREATE TABLE checks (
    receiver    INT,
    payment_id  INT,
    amount  INT
);

INSERT INTO Checks (payment_id, receiver, amount ) VALUES
    ('7', '7', '47 '),
    ('8', '21','42 '),
    ('15','22', '1 '),
    ('6', '7', '26 ');


CREATE TABLE q10_payments (
    payer   INT,
    receiver    INT,
    payment_id  INT,
    Transaction_date    DATE,
    Amount  INT
);

INSERT INTO q10_payments (payer, receiver, payment_id, Transaction_date, Amount ) VALUES
    ('14', '7', '1', '2020-06-30', '45'),
    ('14', '7', '2', '2020-07-28', '17'),
    ('14', '7', '3', '2020-07-29', '24'),
    ('1', '7', '4', '2020-08-27', '4'),
    ('1', '7', '5', '2020-08-28', '89'),
    ('6', '7', '6', '2020-08-29', '26'),
    ('6', '7', '7', '2020-08-30', '47'),
    ('7', '21', '8', '2020-08-31', '42'),
    ('7', '21', '9', '2020-09-01', '52'),
    ('3', '1', '10', '2020-09-04', '31'),
    ('3', '1', '11', '2020-09-05', '92'),
    ('3', '1', '12', '2020-09-06', '54'),
    ('3', '1', '13', '2020-09-07', '84'),
    ('7', '22', '14', '2020-09-08', '23'),
    ('7', '22', '15', '2020-09-09', '1'),
    ('7', '22', '16', '2020-09-10', '63'),
    ('4', '7', '17', '2021-05-28', '75'),
    ('12', '7', '18', '2021-06-30', '94'),
    ('12', '7', '19', '2021-07-28', '25'),
    ('12', '7', '20', '2021-07-29', '80');

תשובות

סרטון הסבר

תשובה 1

Displaying records 1 - 10

payer	receiver	total_received
1	1	4
3	1	31
3	3	84
1	7	89
4	7	75
6	7	73
12	7	199
14	7	62
7	21	42
7	22	87

הצג פתרון

T-SQL

SELECT payer,
  COALESCE(cash.receiver, bit.receiver, Checks.receiver) AS receiver,
  sum(coalesce(cash.amount, bit.amount, Checks.amount)) as total_received
FROM Q10_PAYERS payers
LEFT JOIN Cash on cash.payment_id = payers.payment_id
LEFT JOIN Bit ON BIT.payment_id = payers.payment_id
LEFT JOIN Checks ON Checks.payment_id = payers.payment_id
WHERE COALESCE(cash.receiver, bit.receiver, Checks.receiver) IS NOT NULL
GROUP BY payer, COALESCE(cash.receiver, bit.receiver, Checks.receiver)
-- SELECT * FROM Checks

תשובה 2

Displaying records 1 - 10

source	reference	n_payments_from_source	n_payments_to_source	last_transaction
7	1	0	2	2020-08-28
7	4	0	1	2021-05-28
7	6	0	2	2020-08-30
7	12	0	3	2021-07-29
7	14	0	3	2020-07-29
7	21	2	0	2020-09-01
7	22	3	0	2020-09-10
1	3	0	4	2020-09-07
1	7	2	0	2020-08-28
3	1	4	0	2020-09-07

הצג פתרון

T-SQL, (מראה רק את ה-10 שורות הראשונות)

with entites as (
  select payer as entity
  from q10_payments
  union
  select receiver as entity
  from q10_payments
)

SELECT  ent.Entity as source,
  case when ent.entity = q10.payer then q10.receiver else q10.payer end as reference,
  count(case when ent.entity = q10.payer then payment_id else null end) as n_payments_from_source,
  count(case when ent.entity = q10.receiver then payment_id else null end) as n_payments_to_source,
  max(transaction_date) as last_transaction
FROM entites ent
left join q10_payments q10 on q10.payer = ent.entity OR ent.entity  = q10.receiver
group by ent.Entity,
  case when ent.entity = q10.payer then q10.receiver else q10.payer end
order by count(*) over(partition by ent.Entity) desc