3NPgdzMZ

1DROP TABLE IF EXISTS previous_transactions;
2DROP TABLE IF EXISTS transactions;
3DROP TABLE IF EXISTS users;
4
5CREATE TABLE users (name VARCHAR(10) NOT NULL PRIMARY KEY);
6
7CREATE TABLE transactions(
8    id INT PRIMARY KEY AUTO_INCREMENT,
9    user_from VARCHAR(10) NOT NULL,
10    user_to VARCHAR(10) NOT NULL, 
11    price INT NOT NULL,
12    FOREIGN KEY (user_from) REFERENCES users(name),
13    FOREIGN KEY (user_to) REFERENCES users(name)
14);
15
16CREATE TABLE previous_transactions
17(
18    id INT NOT NULL,
19    previous_id INT NOT NULL,
20    FOREIGN KEY (id) REFERENCES transactions(id),
21    FOREIGN KEY (previous_id) REFERENCES transactions(id)
22);
23
24
25INSERT INTO users(name) VALUES 
26('patient'), ('hospital'), ('broker'), ('company');
27
28INSERT INTO transactions VALUES
29(1, 'patient', 'patient', 1500),
30(2, 'patient', 'hospital', 1500),
31(3, 'hospital', 'company', 480),
32(4, 'hospital', 'hospital', 1020),
33(5, 'company', 'broker', 60),
34(6, 'company', 'company', 420);
35
36INSERT INTO previous_transactions VALUES
37(1, 1),
38(2, 1),
39(3, 2),
40(4, 2),
41(5, 3),
42(6, 3);
43
44INSERT INTO transactions VALUES
45(7, 'patient', 'patient', 1200),
46(8, 'patient', 'hospital', 1200),
47(9, 'hospital', 'hospital', 800),
48(10, 'hospital', 'company', 400);
49
50INSERT INTO previous_transactions VALUES
51(7, 7),
52(8, 7),
53(9, 8),
54(10, 8);
55
56INSERT INTO transactions VALUES
57(11, 'patient', 'patient', 600),
58(12, 'patient', 'company', 560),
59(13, 'patient', 'broker', 40);
60
61INSERT INTO previous_transactions VALUES
62(11, 11),
63(12, 11),
64(13, 11);
65
66INSERT INTO transactions VALUES
67(14, 'company', 'patient', 350),
68(15, 'company', 'company', 70),
69(16, 'patient', 'company', 350);
70
71INSERT INTO previous_transactions VALUES
72(14, 6),
73(15, 6),
74(16, 14);
75
76-- *******************
77
78-- product A:
79--  patient  => hospital: 7300 (profit 5000)
80--  hospital => company:  2300
81-- product B:
82--  patient  => hospital: 1000 (profit 600)
83--  hospital => company:  400
84-- product C:
85--  patient  => hospital: 3400 (profit 3000)
86--  hospital => company:  400
87
88-- discount(A, B):
89--  hospital => patient: 500
90
91-- total: (5000 + 2300) + (600 + 400) + (400 + 3000) - 500 = 11200
92-- tax fee: 896
93--  patient => hospital: 869
94-- card fee: 363
95--  company => broker: 363
96
97-- patient: 11200 + 896 = 12096
98-- company  profit: (2300 + 400 + 400 - 363) = 2737
99-- hospital profit: (5000 + 600 + 3000 + 896 - 500) = 8996
100-- broker   protit: 363
101
102INSERT INTO transactions VALUE
103
104-- product
105(18, 'patient', 'patient', 7300),
106(19, 'patient', 'hospital', 7300),
107(20, 'hospital', 'company', 2300),
108(21, 'hospital', 'hospital', 5000),
109(22, 'patient', 'patient', 1000),
110(23, 'patient', 'hospital', 1000),
111(24, 'hospital', 'company', 400),
112(25, 'hospital', 'hospital', 600),
113(26, 'patient', 'patient', 3400),
114(27, 'patient', 'hospital', 3400),
115(28, 'hospital', 'company', 400),
116(29, 'hospital', 'hospital', 3000),
117
118-- product sum
119(31, 'company', 'company', 3100),
120(32, 'hospital', 'hospital', 8600),
121
122-- discount of product
123(33, 'hospital', 'patient', 500),
124(34, 'hospital', 'hospital', 8100),
125
126-- total
127(35, 'hospital', 'hospital', 8100),
128(36, 'company', 'company', 3100),
129
130-- tax fee
131(37, 'patient', 'patient', 896), -- ((8100 + 3100) * 0.08)
132(38, 'patient', 'hospital', 896),
133
134-- total sum included tax fee
135(39, 'hospital', 'hospital', 8996),
136(40, 'company', 'company', 3100),
137
138-- card fee
139(41, 'company', 'broker', 363), -- ((8996 + 3100) * 0.03).upper
140(42, 'company', 'company', 2737);
141
142INSERT INTO previous_transactions VALUE
143(18, 18),
144(19, 18),
145(20, 19),
146(21, 19),
147(22, 22),
148(23, 22),
149(24, 23),
150(25, 23),
151(26, 26),
152(27, 26),
153(28, 27),
154(29, 27),
155(31, 20),
156(31, 24),
157(31, 28),
158(32, 21),
159(32, 25),
160(32, 29),
161(33, 32),
162(34, 32),
163(35, 34),
164(36, 31),
165(37, 37),
166(38, 37),
167(39, 38),
168(39, 35),
169(40, 36),
170(41, 40),
171(42, 40);
172
173
174-- *******************
175
176SET SESSION cte_max_recursion_depth = 1000000;
177SET SESSION sql_mode= 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
178
179SELECT t.*, p.previous_id FROM transactions AS t INNER JOIN previous_transactions AS p ON t.id = p.id;
180
181-- 特定のトランザクションの履歴
182WITH RECURSIVE transaction_history AS
183(
184   SELECT 1 AS id
185   UNION ALL
186   SELECT pt.id FROM transaction_history AS th INNER JOIN previous_transactions AS pt ON pt.previous_id = th.id WHERE pt.id != th.id
187), extracted_transactions AS (
188  SELECT t.* FROM transaction_history AS h INNER JOIN transactions AS t ON t.id = h.id
189)
190SELECT * FROM extracted_transactions;
191
192-- unspending transactions 
193SELECT * FROM transactions AS t WHERE (SELECT COUNT(*) FROM previous_transactions AS pt WHERE pt.previous_id = t.id) = 0;
194
195-- spended transactions
196SELECT t1.* FROM transactions AS t1 LEFT OUTER JOIN (SELECT * FROM transactions AS t WHERE (SELECT COUNT(*) FROM previous_transactions AS pt WHERE pt.previous_id = t.id) = 0) AS t2 ON t2.id = t1.id WHERE t2.id IS NULL;
197
198-- node
199SELECT * FROM previous_transactions AS pt INNER JOIN transactions AS t1 ON t1.id = pt.id INNER JOIN transactions AS t2 ON t2.id = pt.previous_id;
200
201-- all node line
202SELECT t1.*, t2.*, pt.* FROM previous_transactions AS pt INNER JOIN transactions AS t1 ON t1.id = pt.id INNER JOIN transactions AS t2 ON t2.id = pt.previous_id;
203
204-- root node
205SELECT * FROM transactions AS t INNER JOIN previous_transactions AS pt ON pt.id = t.id WHERE ((user_from = user_to) AND (pt.previous_id = t.id));
206
207-- 金額の整合性チェック, ルートノードだけ湧き出し（金額の不一致）が起こっている
208SELECT pt.id, SUM(t1.price) AS output, (SELECT SUM(t2.price) FROM previous_transactions AS pt INNER JOIN transactions AS t2 ON t2.id = pt.previous_id WHERE pt.id = t1.id) AS input FROM previous_transactions AS pt INNER JOIN transactions AS t1 ON t1.id = pt.id GROUP BY pt.previous_id;
209
210-- 特定のトランザクションから生成されたアカウントの口座ごとの割合
211WITH RECURSIVE transaction_history AS
212(
213   SELECT t.id, t.user_from AS root_user
214     FROM transactions AS t WHERE t.id = 1
215   UNION ALL
216   SELECT pt.id, th.root_user
217     FROM transaction_history AS th
218     INNER JOIN previous_transactions AS pt ON pt.previous_id = th.id
219     WHERE pt.id != th.id
220), extracted_transactions AS (
221  SELECT t.*, th.root_user FROM transaction_history AS th INNER JOIN transactions AS t ON t.id = th.id
222), unspending_transactions AS (
223  SELECT * FROM transactions AS t WHERE (SELECT COUNT(*) FROM previous_transactions AS pt WHERE pt.previous_id = t.id) = 0
224)
225SELECT et.id, et.root_user AS user_from, et.user_to AS user_to, SUM(et.price)
226  FROM extracted_transactions AS et INNER JOIN unspending_transactions AS ut ON et.id = ut.id GROUP BY user_from, user_to
227
228-- 総額の請求金額
229SELECT (SELECT SUM(t1.price) FROM transactions AS t1 WHERE t1.user_from = 'patient' AND t1.user_to = 'patient') - 
230       (SELECT SUM(t.price) FROM transactions AS t WHERE (SELECT COUNT(*) FROM previous_transactions AS pt WHERE pt.previous_id = t.id) = 0 AND t.user_to = 'patient') AS total_price;
231
232-- 病院の総額とコスト
233SELECT (SELECT SUM(price) FROM transactions WHERE user_from = 'patient' AND user_to =  'hospital') AS sales, (SELECT SUM(price) FROM transactions WHERE user_from = 'hospital' AND user_to != 'hospital') AS cost;
234
235-- 会社の総額とコスト
236SELECT (SELECT SUM(price) FROM transactions WHERE user_from = 'hospital' AND user_to =  'company') AS sales, (SELECT SUM(price) FROM transactions WHERE user_from = 'company' AND user_to != 'company') AS cost;
237
238-- 病院の売上とコストの内訳
239SELECT t1.*, t1.id, pt.previous_id AS pid FROM transactions AS t1 INNER JOIN previous_transactions AS pt ON pt.id = t1.id  WHERE (user_to =  'hospital' OR user_from = 'hospital') AND NOT (user_to = 'hospital' AND user_from = 'hospital');
240
241-- 特定のトランザクションを全て逆に辿る
242WITH RECURSIVE transaction_logs AS
243(
244   SELECT *, CAST(t.id AS CHAR(1000)) AS path, 0 AS depth FROM transactions AS t WHERE t.id = 39
245   UNION ALL
246   SELECT t1.*, CONCAT(tl.path, ',', CAST(t1.id AS CHAR(10))), tl.depth + 1
247     FROM transaction_logs AS tl
248     INNER JOIN previous_transactions AS pt ON pt.id = tl.id
249     INNER JOIN transactions AS t1 ON pt.previous_id = t1.id
250     WHERE t1.id != tl.id
251)
252 SELECT * FROM transaction_logs ORDER BY depth ASC;