哪些 SQL 查询有助于分析会员基础？

Question

在我们的体育中心，我想分析我们会员的订阅数量和类型，但我在精确的 SQL 查询方面遇到了麻烦。

“订阅”是一种入场通行证，可让您参加我们体育中心的特定活动。它们有开始日期和结束日期，代表它们的有效期限。他们有一项相关活动（例如“健身房”、“游泳”、“团体健身”、“攀岩”等）。

一名会员可以拥有多个订阅。通常，一个订阅会在购买下一个订阅之前过期，但他们可以拥有两个（或更多）有效（或当前）订阅。

稍微简化一下，我们有以下两个表：

  Members - stores a member's data, has these columns
    ID (int) - unique ID for a member
    Name (varchar) - the member's name
    Gender (varchar) - the member's gender
    NumGym (int) - number of valid gym passes a member has (derived)

目前

  Subscriptions - holds all the subscriptions we've sold, has these columns
    TypeID (varchar) - unique ID for a subscription type (specifies activity)
    MemberID (int) - the ID of the member that purchased this subscription
    StartDate (datetime) - when the subscription is valid from
    EndDate (datetime) - when the subscription is valid to

我想问的一些问题是：

1. 有多少会员拥有有效的“健身房”通行证？
2. 对于（1），我在某一天有多少活跃会员

，我尝试了以下操作：

declare @aDay datetime
set @aDay = convert(datetime, '2009-12-08', 102)

update Members m
set NumGym = (select count(memberid)
    from Subscriptions s
    where s.MemberId = m.Id
    and s.TypeID = "Gym"
    and @aDay between s.StartDate and s.EndDate)

但是它似乎没有产生正确的结果（基于我们对会员资格的理解和一些手动计数）。

由于无法让（1）起作用，我还没有尝试过（2）。

对于产生我想要的结果的查询有什么建议吗？

Answer 1

1. 目前有多少会员拥有有效的“健身房”通行证？

使用：

SELECT COUNT(*)
  FROM SUBSCRIPTIONS s
 WHERE GETDATE() BETWEEN s.startdate AND s.enddate
   AND s.typeid = 'Gym'

GETDATE() 是一个 SQL Server 函数，您可以调用获取当前日期（和时间），而不是构造日期。

2. 某一天我有多少活跃会员（即有多少有效通行证）

使用：

SELECT COUNT(*)
  FROM SUBSCRIPTIONS s
 WHERE @arbitrary_date BETWEEN s.startdate AND s.enddate

Answer 2

当前有多少会员拥有有效的“健身房”通行证

SELECT COUNT(*)
    FROM Subscriptions
    WHERE CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME) BETWEEN StartDate AND EndDate
          AND TypeID = 'Gym'

您应该在此处截断 GETDATE() ，因为按照我的理解，您希望包含 EndDate 。

“有多少会员？”与“多少次通过？”不同。

那么“有多少会员？”：

SELECT COUNT(DISTINCT MemberID)
    FROM Subscriptions
    WHERE @Date BETWEEN StartDate AND EndDate

还有“多少通行证？”：

SELECT COUNT(*)
    FROM Subscriptions
    WHERE @Date BETWEEN StartDate AND EndDate