如何限制表上的计数

Question

我想做一个限制计数或能够找到多少次用户与一个网站进行了互动与上限。因此，如果用户数量达到上限，给出最大金额，如果只有一定数量的用户访问了上限，我想知道这个数字以及日期限制在1月4日。

USERINSTANCE

<表类> UserID 实例 CapID 日期 tbody><<tr>1行101/02/20222行101/02/20223行101/02/20224行101/02/20225行101/02/20221行201/02/20226行101/02/20222行201/02/20227行101/02/20228行101/02/20229行101/03/202210行101/03/202211行101/02/202212行101/02/202213行101/03/20223行201/03/20224行201/03/20225行201/04/20226行201/04/202214行101/04/2022

Michael已经演示了如何通过SUM &IFF，然后使用LEAST来做封顶。

还有一个内置函数COUNT_IF，它甚至更整洁:

因此，对于数据的一些CTE:

with USERINSTANCE(UserID, Instance, CapID, date) as (
select column1, column2, column3, to_date(column4, 'mm/dd/yyyy') from values
(1  ,'row'  ,1, '01/02/2022'),
(2  ,'row'  ,1, '01/02/2022'),
(3  ,'row'  ,1, '01/02/2022'),
(4  ,'row'  ,1, '01/02/2022'),
(5  ,'row'  ,1, '01/02/2022'),
(1  ,'row'  ,2, '01/02/2022'),
(6  ,'row'  ,1, '01/02/2022'),
(2  ,'row'  ,2, '01/02/2022'),
(7  ,'row'  ,1, '01/02/2022'),
(8  ,'row'  ,1, '01/02/2022'),
(9  ,'row', 1, '01/03/2022'),
(10,'row', 1, '01/03/2022'),
(11,'row',  1, '01/02/2022'),
(12,'row',  1, '01/02/2022'),
(13,'row',  1, '01/03/2022'),
(3  ,'row'  ,2, '01/03/2022'),
(4  ,'row'  ,2, '01/03/2022'),
(5  ,'row'  ,2, '01/04/2022'),
(6  ,'row', 2, '01/04/2022'),
(14,'row',  1, '01/04/2022')
), caplimit(limitcap, capid, tracker) as (
select * from values
(10, 1, 'sales'),
(5, 2, 'invite')
)

和SQL:

SELECT 
c.tracker, 
c.LimitCap, 
LEAST(count_if(date < '2022-01-04'), c.LimitCap) AS ActualCount, 
COUNT(Instance) AS TotalCount
FROM USERINSTANCE AS u
JOIN CAPLIMIT AS c 
ON u.CapID = c.CapID
GROUP BY 1,2;

我们得到:

tbody> <<tr>

TRACKER	LIMITCAP	ACTUALCOUNT	TOTALCOUNT
销售	10	10	14
邀请	5	4	6

试试这样:

SELECT c.tracker, c.LimitCap, LEAST(SUM(IFF(date < '01/04/2022'::date, 1, 0)), c.LimitCap) AS ActualCount, COUNT(Instance) AS TotalCount
FROM USERINSTANCE AS u
JOIN CAPLIMIT AS c ON u.CapID = c.CapID
GROUP BY c.tracker, c.LimitCap;