A method to analyze the distribution of voucher usage. Python implementation version (on)

First, describe the scenario. colleagues in the marketing department are engaged in activities recently. The total amount of coupons received in the previous week is less than the amount received in the last day. So let me check the details of this voucher, including but not limited to: 1. which users have received 100,000 vouchers? 2. Sort the order from the top to the bottom according to the user's receipt. 3. What coupons have been used? 4. What is the total amount of coupons used? The attachment contains a text of 100,000 lines, each of which is a voucher number.

The first thing to do is to analyze the requirements of Marketing Department colleagues. I need to do the following: first, find out which vouchers have been received by users. Second, calculate the number of coupons each user receives. Third, which vouchers are used in the transaction, and the details of the transaction. Fourth, which of the following are on the blacklist? Fifth, the user registration time and IP address used for registration outside the blacklist.

Because the voucher number is saved by row, write a py file that reads the number by row, organize it to query which SQL statements are collected by users, and then execute SQL statements in batches, so the first py file getidcountsql. PY generates:

#! /Usr/bin/ENV Python # Coding = utf-8import sysimport OS if _ name _ = "_ main _": If Len (SYS. argv )! = 2: Print "Usage: Python getnum. PY [target file] \ n "print" Example: Python ", sys. argv [0], "code.txt" sys. exit (1) filename = sys. argv [1] If OS. path. exists (filename): file_object = open (filename) lines = file_object.readlines () for line in lines: line = line. strip ('\ n') # Remove the ending line print "select ID, count from tb_gift where code =' % s';" % line file_object.close ()

Run Python getidcountsql. py code.txt> getidcount. SQL

Therefore, an SQL script is generated as follows:

Select ID, count from tb_gift where code = 'code123 ';
Select ID, count from tb_gift where code = 'code12 ';
Select ID, count from tb_gift where code = 'code456 ';

Run the SQL script and write the result to a text file: mysql-h192.168.1.12-p3307-ussergsw-p123pwd-dsserdb <getidcount. SQL> giftidcount.txt
At this time, a text file with a column header will be generated, similar to: Id count
Code123 1
Id count
Code12 1
At this time, although it can be processed, the column names in each row are always uncomfortable, so I checked the information and removed the column names: mysql-h192.168.1.12-p3307-ussergsw-p123pwd-dsserdb -- disable-column-names <getidcount. SQL
> Giftidcount.txt
In this way, the number of lines of text files is half of that of the file. code123 1
Code121
The following shows the activated coupons, that is, the second column is 1, so the second py file is available:
#! /Usr/bin/ENV Python
# Coding = UTF-8

Import sys
Import OS

If _ name _ = "_ main __":
If Len (SYS. argv )! = 2:
Print "Usage: Python getnum. py [target file] \ n"
Print "Example: Python", SYS. argv [0], "a.txt"
SYS. Exit (1)

Filename = SYS. argv [1]
Map = {}
If OS. Path. exists (filename ):
File_object = open (filename)
Lines = file_object.readlines ()
For line in lines:
Line = line. Strip ('\ n') # Remove the line feed at the end.
Cols = line. Split ('\ t') # text is a tab Separator
If Len (Cols) = 2 and Cols [0]! = "ID" and Cols [1] = '1 ':
Print "select user_id from tb_user_gift where id = % s;" % Cols [0]
File_object.close ()

Run Python getuseridsql. PY giftidcount.txt> getuserid. run mysql-h192.168.1.12-p3307-ussergsw-p123pwd-dsserdb -- disable-column-names <getuserid. SQL> userid.txt: 1113222123 Collect User Information statisticuserids. PY :#! /Usr/bin/ENV Python
# Coding = UTF-8

Import sys
Import OS

Def parse_line (line ):
# User_id
#11132
# Ignore in the first row. For data with column names, if the column name is not included in the first row, the ID of the second row user. The number is 1.
Try:
Result = {}
If not line or line. Find ("user_id ")! =-1:
Return 0, 0
Else:
Line = line. Strip ('\ n ')
Productid = line
Num = 1

Return productid, num

Failed t exception, MSG:
Print MSG, ', line =', line
Return 0, 0

Def reverse_numeric (x, y ):
Return y [1]-X [1]

If _ name _ = "_ main __":
If Len (SYS. argv )! = 2:
Print "Usage: Python getnum. py [target file] \ n"
Print "Example: Python", SYS. argv [0], "a.txt"
SYS. Exit (1)

Filename = SYS. argv [1]
Map = {}
If OS. Path. exists (filename ):
File_object = open (filename)
Lines = file_object.readlines ()
For line in lines:
# Print 'current line = ', line
Productid, num = parse_line (line)
Count = map. Get (productid)
If count:
Map [productid] = count + int (Num)
Else:
Map [productid] = int (Num)

Arr = [V for V in sorted (Map. Items (), CMP = reverse_numeric)]
Print arr

Execute Python statisticuserids. py userid.txt> usercount.txt. The statistics are as follows: ('123', 5), ('123', 3), ('123', 1)
In this way, the user's statistical information is available, and the transaction information will be discussed next time.

Previous Article: MySQL initializes the root password and allows remote access

Next article: Why is the csdn resource page suspended?

Top

2

Step on

0