peewee mysql 查询用户连续支付天数，支付记录中一天内会有多条支付订单。-Go服务器开发

如果支付记录中一天内会有多条支付订单，我们需要对每个用户的支付记录按照日期和订单号进行去重，然后再执行查询连续支付天数的语句。

假设我们有以下的表结构来记录用户支付情况：

CREATE TABLE payments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    payment_date DATE NOT NULL,
    order_no VARCHAR(20) NOT NULL,
    UNIQUE(user_id, payment_date, order_no)
);

其中，user_id表示用户的ID，payment_date表示支付日期，order_no表示订单号。我们可以使用以下的SQL语句来查询每个用户的连续支付天数：

SELECT user_id,
       COUNT(*) AS consecutive_days
FROM
  (SELECT user_id,
          payment_date,
          ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY payment_date) - 
              ROW_NUMBER() OVER (PARTITION BY user_id, DATE_SUB(payment_date, INTERVAL 1 DAY) 
                                 ORDER BY payment_date) AS diff
   FROM
     (SELECT DISTINCT user_id, payment_date, order_no FROM payments) t1) t2
GROUP BY user_id,
         diff
HAVING consecutive_days > 0
ORDER BY user_id,
         consecutive_days DESC;

该查询语句与之前查询连续支付天数的语句类似，只是在内层查询中加入了去重的逻辑。

在Peewee中，可以使用以下方式来执行该查询语句：

from peewee import *
db = MySQLDatabase('my_database', user='my_username', password='my_password',
                   host='127.0.0.1', port=3306)
class Payments(Model):
    user_id = IntegerField()
    payment_date = DateField()
    order_no = CharField(max_length=20)
    class Meta:
        database = db
        constraints = [SQL('UNIQUE(user_id, payment_date, order_no)')]
query = """
    SELECT user_id,
           COUNT(*) as consecutive_days
    FROM
      (SELECT user_id,
              payment_date,
              ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY payment_date) - 
                  ROW_NUMBER() OVER (PARTITION BY user_id, DATE_SUB(payment_date, INTERVAL 1 DAY) 
                                     ORDER BY payment_date) AS diff
       FROM
         (SELECT DISTINCT user_id, payment_date, order_no FROM payments) t1) t2
    GROUP BY user_id,
             diff
    HAVING consecutive_days > 0
    ORDER BY user_id,
             consecutive_days DESC;
"""
result = db.execute_sql(query)
for row in result.fetchall():
    print(row[0], row[1])