Intro
Hi everyone, Happy Lunar New Year! In the previous post, we've customized the text analysis configurations and received greetings from SAP HANA. Do you remember the characters of "Wish you prosperity"? Yeah, you got it. That's "恭喜发财", a very popular greeting in Chinese new year. In China, people especially children sometimes jokingly use the phrase "恭喜发财，红包拿来" translated as "Wish you prosperity, now give me a red envelop!" So what's red envelop? You can find its meaning from Wikipedia. Red envelop is a monetary gift, also known as red packet or Hongbao (红包 in Chinese). You can learn how to give red envelops from Chinese Red Packets and Envelopes, Lucky Money during Chinese New Year.
With the rapid development of WeChat (China's most popular mobile chat app, someone says it's Chinese Whatsapp, but someone doesn't, see here and here) in China, most people give virtual red envelops via WeChat in Chinese new year 2015 (See Tencent's WeChat Sends 1 Billion Virtual Red Envelopes On New Year's Eve - Forbes for more details). So in this post, let's have a look at the game of WeChat Hongbao and try to simulate it with stored procedure in SAP HANA.
WeChat Hongbao
There are several kinds of red envelops in WeChat. Among them the most exciting one is giving red envelops in chat groups. So what does that mean? Since WeChat has the "chat group" feature, users can create chat groups and invite contacts to join the groups. So now you can give red envelops in the chat group and let the group members get your red envelops. The following is an example of mine. I wanted to give red envelops in one chat group of mine and let the group members to get them. The game is very easy including only two steps, giving and getting.
Step 1 (for giver in the chat group): Giving red envelops in the chat group with the following substeps
- Input the # of red envelops (e.g., 5 in the screen shot)
- Input the total amount (e.g., 100)
- Choose the mode (Random or average, default is random since it's more interesting and exciting. We just discuss random in this post)
- Choose the method of payment
- Confirm to give by clicking the button at the bottom
Step 2 (for all group members in the chat group): Getting red envelops
Everyone (including the giver) in the chat group can get one red envelop by clicking the red envelop in the chat screen. In addition, all group members can check the result of getting red envelops as follows. You can find a list at the bottom which shows you all current getters and how much they get. After all red envelops are given, you can find the complete list of all getters and who's the champion. Above the list, you can see some figures you set in step 1, like # of red envelops, total amount and game time.
That's it, very easy. If we choose the average mode, everyone will get 100 / 5 = 20. Let' have a look at the random figures. 35.84 + 5.21 + 34.00 + 17.42 + 7.53 = 100. From the result we can observe that two people are lucky, since 35.84 and 34.00 are much higher than the average 20; one people (17.42) is just soso and two people are not lucky since 5.21 and 7.53 are much lower than the average. That's the magic of random red envelops, since it's more exciting than the average. We all love it.
Now let's rethink the game and guess what happened in the backend. For low latency, when the giver gives out the red envelops, the random amounts should be calculated immediately and stored in an array or some other data structure in the backend. When group members get red envelops, the amounts pop up until the last one. So how much you can get only depends on the order and the amount is not calculated when you trigger getting, while the total amount is divided randomly in advance, at creating the game. After hundreds of games, I found some potential rules as follows.
- Rule 1: The sum of all red envelops equals the total amount.
- Rule 2: There is no empty red envelop which means the minimum is 0.01.
- Rule 3: Keep random, but avoid huge gap. For example, there's total amount 100 with 10 red envelops. If one get 99, other nine people can get only 1 totally. It's boring for those nine people and they have no interest to get red envelops.
- Rule 4: The order doesn't matter. For example there are 100 red envelops, the first one may get the most, may also get the least and the possibility is the same. Someone thinks the first one may have higher possibility to get big money than the last one. Nope. According to my experience absolutely NO. As I said the possibility is the same. Just like lucky draw, everyone has chance to get the biggest, and the possibility is same to everyone.
Problem
Now we can define our problem and simulate the game in SAP HANA with stored procedure. So, the problem can be defined as given the total amount, # of red envelops and the minimum these three parameters, please return these random red envelops in an order based on the rules above. So, the potential stored procedure should look like the following.
CREATE PROCEDURE HONGBAO (
IN AMOUNT DECIMAL(10, 2),
IN NUM INTEGER,
IN MIN DECIMAL(10, 2),
OUT HONGBAO_RESULT TABLE(SEQ INTEGER, AMOUNT DECIMAL(10, 2)))
LANGUAGE SQLSCRIPT
READS SQL DATA AS
BEGIN
...
END;
After creating the stored procedure, we should be able to call it to get the result. Here is an example. The champion is in red box.
CALL HONGBAO(100, 5, 0.01, ?);
There're already several solutions to solve this problem, but I don't think they obey the rules well. The ideas of the following solution 1 and 2 came from this article (Sorry it's in Chinese :sad: ). I made some modifications and translated them into SAP HANA SQLScript. Finally I improved solution 2 and got my solution 3.
Simulating WeChat's Hongbao - Solution 1
Idea: For example, total amount 100, 5 red envelops, minimum 0.01. The first red envelop randoms from 0.01 to 100 - 0.04 = 99.96 (because of rule 2, you need to leave at least 0.04 for the rest four red envelops). Then the second one randoms from 0.01 to 100 - 0.03 - red envelop #1 and so on and so forth.
DROP PROCEDURE HONGBAO;
CREATE PROCEDURE HONGBAO (
IN AMOUNT DECIMAL(10, 2),
IN NUM INTEGER,
IN MIN DECIMAL(10, 2),
OUT HONGBAO_RESULT TABLE(SEQ INTEGER, AMOUNT DECIMAL(10, 2)))
LANGUAGE SQLSCRIPT
READS SQL DATA AS
BEGIN
DECLARE SEQ_ARR INTEGER ARRAY;
DECLARE AMOUNT_ARR DECIMAL(10, 2) ARRAY;
DECLARE I INTEGER;
DECLARE CURR_AMOUNT DECIMAL(10, 2) := :AMOUNT;
DECLARE SAFE_AMOUNT DECIMAL(10, 2);
FOR I IN 1 .. :NUM - 1 DO
SEQ_ARR[:I] := :I;
SAFE_AMOUNT := :CURR_AMOUNT - (:NUM - :I) * :MIN;
AMOUNT_ARR[:I] := TO_DECIMAL(RAND() * :SAFE_AMOUNT + :MIN, 10, 2);
CURR_AMOUNT := :CURR_AMOUNT - :AMOUNT_ARR[:I];
END FOR;
SEQ_ARR[:NUM] := :NUM;
AMOUNT_ARR[:NUM] := :CURR_AMOUNT;
HONGBAO_RESULT = UNNEST(:SEQ_ARR, :AMOUNT_ARR) AS (SEQ, AMOUNT);
END;
After creating the stored procedure successfully, we can now test it.
CALL HONGBAO(100, 5, 0.01, ?);
CALL HONGBAO(10, 10, 0.01, ?);
Have you found the problem? Yes, it breaks rule 3 and rule 4. Because of the algorithm, the first one has the highest possibility to random a big red envelop and become the champion. For simplicity, I did not provide mathematical proofs here, but it's easy to understand, isn't it? If the first several ones already occupy the most, the rest has no chance to get a big red envelop. In short, with this algorithm, the huge gap is possible and the order matters.
Simulating WeChat's Hongbao - Solution 2
Idea: For example, total amount 100, 5 red envelops, minimum 0.01. The first red envelop randoms from 0.01 to (100 - 0.04) / (5 - 1) (4 means how many red envelops need to random). Then the second one randoms from 0.01 to (100 - 0.03 - red envelop #1) / (5 - 2) (now only 3 red envelops need to random) and so on and so forth. So, the only difference between solution 2 and solution 1 is that in solution 2 the upper limit of random is set to the current average of left random red envelops which can prevent from the huge gap.
DROP PROCEDURE HONGBAO;
CREATE PROCEDURE HONGBAO (
IN AMOUNT DECIMAL(10, 2),
IN NUM INTEGER,
IN MIN DECIMAL(10, 2),
OUT HONGBAO_RESULT TABLE(SEQ INTEGER, AMOUNT DECIMAL(10, 2)))
LANGUAGE SQLSCRIPT
READS SQL DATA AS
BEGIN
DECLARE SEQ_ARR INTEGER ARRAY;
DECLARE AMOUNT_ARR DECIMAL(10, 2) ARRAY;
DECLARE I INTEGER;
DECLARE CURR_AMOUNT DECIMAL(10, 2) := :AMOUNT;
DECLARE SAFE_AMOUNT DECIMAL(10, 2);
FOR I IN 1 .. :NUM - 1 DO
SEQ_ARR[:I] := :I;
SAFE_AMOUNT := (:CURR_AMOUNT - (:NUM - :I) * :MIN) / (:NUM - :I);
AMOUNT_ARR[:I] := TO_DECIMAL(RAND() * :SAFE_AMOUNT + :MIN, 10, 2);
CURR_AMOUNT := :CURR_AMOUNT - :AMOUNT_ARR[:I];
END FOR;
SEQ_ARR[:NUM] := :NUM;
AMOUNT_ARR[:NUM] := :CURR_AMOUNT;
HONGBAO_RESULT = UNNEST(:SEQ_ARR, :AMOUNT_ARR) AS (SEQ, AMOUNT);
END;
The only modification is in line 18, we set the upper limit of random to the current average of left random red envelops. Now we can also have a test.
CALL HONGBAO(100, 5, 0.01, ?);
CALL HONGBAO(10, 10, 0.01, ?);
The huge gap seemed to disappear, however there comes a new problem. Because of setting the random upper limit to current average, the first one has much lower possibility to become the champion. Do you know why? So, our rule 4 is broken. The order matters and no one is willing to get the first red envelop. After my observation, there's high possibility the champion appears in the second half. I think we can prove it with mathematics.
Simulating WeChat's Hongbao - Solution 3
Idea: Here comes my improved solution 3. We just need to shuffle the result in solution 2 in order to avoid the importance of order. So, we added line 22 - 24.
DROP PROCEDURE HONGBAO;
CREATE PROCEDURE HONGBAO (
IN AMOUNT DECIMAL(10, 2),
IN NUM INTEGER,
IN MIN DECIMAL(10, 2),
OUT HONGBAO_RESULT TABLE(SEQ INTEGER, AMOUNT DECIMAL(10, 2)))
LANGUAGE SQLSCRIPT
READS SQL DATA AS
BEGIN
DECLARE SEQ_ARR INTEGER ARRAY;
DECLARE AMOUNT_ARR DECIMAL(10, 2) ARRAY;
DECLARE I INTEGER;
DECLARE CURR_AMOUNT DECIMAL(10, 2) := :AMOUNT;
DECLARE SAFE_AMOUNT DECIMAL(10, 2);
FOR I IN 1 .. :NUM - 1 DO
SEQ_ARR[:I] := :I;
SAFE_AMOUNT := (:CURR_AMOUNT - (:NUM - :I) * :MIN) / (:NUM - :I);
AMOUNT_ARR[:I] := TO_DECIMAL(RAND() * :SAFE_AMOUNT + :MIN, 10, 2);
CURR_AMOUNT := :CURR_AMOUNT - :AMOUNT_ARR[:I];
END FOR;
SEQ_ARR[:NUM] := :NUM;
AMOUNT_ARR[:NUM] := :CURR_AMOUNT;
AMOUNT_T = UNNEST(:AMOUNT_ARR) AS (AMOUNT);
AMOUNT_T = SELECT * FROM :AMOUNT_T ORDER BY RAND();
AMOUNT_ARR := ARRAY_AGG(:AMOUNT_T.AMOUNT);
HONGBAO_RESULT = UNNEST(:SEQ_ARR, :AMOUNT_ARR) AS (SEQ, AMOUNT);
END;
OK. Let's also have a test.
CALL HONGBAO(100, 5, 0.01, ?);
CALL HONGBAO(10, 10, 0.01, ?);
That's it! Everything looks good now. :smile:
Conclusion
Till now we've simulated WeChat Hongbao with stored procedrue in SAP HANA. We can now get virtual red envelops from SAP HANA. Have fun. :cool:
Hope you enjoyed reading my blog and get more red envelops in WeChat. If you have better solutions, please share with me. Thank you very much. :smile:
- SAP Managed Tags:
