计数（月底不同

发布于 2025-01-08 13:42:25 字数 500 浏览 0 评论 0原文

我有一个数据库，其中包含 TSTAMP USERNAME SUBCR_TYPE 和 BALANCE 的日志。我想知道有多少用户在过去的每个月底都有 SUBSCR_TYPE 的正余额

结果表应该如下所示

         silver|gold|platinum
2011-09      34|56  |109
2011-10      23|43  |67
2011-11      33|56  |45
.
.
.

我已经尝试过，结果明显错误，

SET @ts = unix_timestamp(LAST_DAY('2011-09-01'));
SELECT COUNT(DISTINCT USERNAME) AS 'silver'
FROM accLog_table 
WHERE BALANCE>=1 
AND SUBSCR_TYPE = 'silver'
AND TSTAMP<@ts

我该如何正确执行此操作？

原文

I have a db with logs of TSTAMP USERNAME SUBCR_TYPE and BALANCE.
I want to know how many users had at each past end of month a positive BALANCE by SUBSCR_TYPE

The resulting table should look like this

         silver|gold|platinum
2011-09      34|56  |109
2011-10      23|43  |67
2011-11      33|56  |45
.
.
.

I have tried this with obviously wrong results

SET @ts = unix_timestamp(LAST_DAY('2011-09-01'));
SELECT COUNT(DISTINCT USERNAME) AS 'silver'
FROM accLog_table 
WHERE BALANCE>=1 
AND SUBSCR_TYPE = 'silver'
AND TSTAMP<@ts

how can I do this correctly?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

浅紫色的梦幻 2025-01-15 13:42:25

我很高兴发布对我有用的解决方案

     SET @ts = unix_timestamp(LAST_DAY('2011-09-01'));       
     SET @subcr = 'silver';
            INSERT 
            INTO monthlyLiveAccess (
                timePeriod,
                silver
            )
            SELECT DATE_FORMAT(FROM_UNIXTIME(@ts), "%Y-%m") AS timePeriod,
                COUNT(*) AS silver FROM (
                SELECT t.* from(
                    SELECT DATE_FORMAT(FROM_UNIXTIME(DATE_TIME), "%Y-%m %H:%i:%s") AS "timePeriod", 
                    USERNAME, BALANCE
                    from accLog_table
                    WHERE N_BALANCE>1
                    AND DATE_TIME<@ts
                    AND SUBSCR='silver'
                ORDER BY timePeriod desc) as t
                GROUP BY USERNAME) AS t1
            ON DUPLICATE KEY UPDATE silver = VALUES(silver);

I happy to post a solution that worked for me

     SET @ts = unix_timestamp(LAST_DAY('2011-09-01'));       
     SET @subcr = 'silver';
            INSERT 
            INTO monthlyLiveAccess (
                timePeriod,
                silver
            )
            SELECT DATE_FORMAT(FROM_UNIXTIME(@ts), "%Y-%m") AS timePeriod,
                COUNT(*) AS silver FROM (
                SELECT t.* from(
                    SELECT DATE_FORMAT(FROM_UNIXTIME(DATE_TIME), "%Y-%m %H:%i:%s") AS "timePeriod", 
                    USERNAME, BALANCE
                    from accLog_table
                    WHERE N_BALANCE>1
                    AND DATE_TIME<@ts
                    AND SUBSCR='silver'
                ORDER BY timePeriod desc) as t
                GROUP BY USERNAME) AS t1
            ON DUPLICATE KEY UPDATE silver = VALUES(silver);

回复收藏 0 原文

~没有更多了~