在结果集中计数实例

发布于 2025-02-03 16:29:24 字数 639 浏览 1 评论 0原文

我正在做一个查询，以监视给定时间范围内的紧急医疗服务的使用情况，在这种情况下，四分之一一年。当我尝试进行计算访问量的子查询时，它会在某些情况下提取最多4000个客户记录的索赔。我想要的只是过去91天内从紧急服务产生的索赔数量，通常不超过10个。

以下是查询的相关部分，我不确定评论：

SELECT
    UMI,
    PIN,
    Agreement,
    --sq.VisitCount,
    med.Code,
    Date
    
FROM main
/*
    INNER JOIN (
    SELECT Agreement, COUNT(*) as VisitCount
    FROM main
    GROUP BY Agreement
    ) AS sq ON main.Agreement = sq.Agreement
*/
--Inner Joins that work are excluded
    
WHERE 
    med.Code BETWEEN '99281' AND '99285'
    AND Date >= (CURRENT_DATE - 91)
    --AND VisitCount >= 3
--GROUP BY Agreement
ORDER BY VisitCount DESC
    ;

原文

I'm doing a query to monitor over usage of emergency medical services in a given time frame, in this case a quarter of a year. When I try to do a subquery that calculates the VisitCount, it pulls every claim recorded for that customer, up to 4000 in some cases. All I want are the number of claims generated from emergency services within the past 91 days, usually no more than 10.

Here are the relevant parts of the query with what I'm unsure of commented out:

SELECT
    UMI,
    PIN,
    Agreement,
    --sq.VisitCount,
    med.Code,
    Date
    
FROM main
/*
    INNER JOIN (
    SELECT Agreement, COUNT(*) as VisitCount
    FROM main
    GROUP BY Agreement
    ) AS sq ON main.Agreement = sq.Agreement
*/
--Inner Joins that work are excluded
    
WHERE 
    med.Code BETWEEN '99281' AND '99285'
    AND Date >= (CURRENT_DATE - 91)
    --AND VisitCount >= 3
--GROUP BY Agreement
ORDER BY VisitCount DESC
    ;

分享到QQ

分享到微博

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

发布评论

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

孤千羽 2025-02-10 16:29:24

SELECT Agreement, COUNT(*) as VisitCount
FROM main
GROUP BY Agreement

将在表中获取每个协议的所有计数（因为没有whene条件）。要限制计数日期，您需要在子查询中进行该条件：

SELECT Agreement, COUNT(*) as VisitCount
FROM main
WHERE Date >= (CURRENT_DATE - 91)
GROUP BY Agreement

避免复制条件（将其在您的内部和外部查询中），您可以使用CTE（大多数现代DBMSES支持这一点）：

WITH mainbydate AS
(
 SELECT *
 FROM main
 WHERE Date >= (CURRENT_DATE - 91)
)
SELECT *
FROM mainbydate INNER JOIN (
  SELECT Agreement, COUNT(*) as VisitCount
  FROM mainbydate
  GROUP BY agreement
 ) sq ON mainbydate.agreement = sq.agreement
WHERE med.Code BETWEEN '99281' AND '99285'

SELECT Agreement, COUNT(*) as VisitCount
FROM main
GROUP BY Agreement

will get all the counts for each agreement in the table (since there are no WHERE conditions). To limit by date in the counts, you need that condition in the subquery:

SELECT Agreement, COUNT(*) as VisitCount
FROM main
WHERE Date >= (CURRENT_DATE - 91)
GROUP BY Agreement

To avoid duplicating the condition (having it in your inner and outer query), you could use a CTE (most modern DBMSes support this):

WITH mainbydate AS
(
 SELECT *
 FROM main
 WHERE Date >= (CURRENT_DATE - 91)
)
SELECT *
FROM mainbydate INNER JOIN (
  SELECT Agreement, COUNT(*) as VisitCount
  FROM mainbydate
  GROUP BY agreement
 ) sq ON mainbydate.agreement = sq.agreement
WHERE med.Code BETWEEN '99281' AND '99285'

回复收藏 0 原文

~没有更多了~