mysql - 脚本卡住了。想要取1个月前到60个月前 "Women"的平均计数。这部分代码让我很困惑

Question

/*This is the code Im using to count and take the Average?*/
`SELECT GETDATE() AS CurrentDate,
   (SELECT COUNT(1) FROM People WITH(NOLOCK) LEFT JOIN LinkPeopleToCompanies WITH(NOLOCK) ON 
   People.PeopleID = LinkPeopleToCompanies.PeopleID
   WHERE (LinkPeopleToCompanies.ToDate >= GETDATE() OR LinkPeopleToCompanies.ToDate IS NULL)
   AND LinkPeopleToCompanies.SinceDate <= GETDATE() - 2190
   AND LinkPeopleToCompanies.CompaniesID = Companies.CompaniesID) AS TotalDirectorsAddedin6years,
       (SELECT AVG(DATEDIFF("yyyy",People.BirthDay,GETDATE())) FROM People WITH(NOLOCK) LEFT JOIN 
   LinkPeopleToCompanies WITH(NOLOCK) ON People.PeopleID = LinkPeopleToCompanies.PeopleID
   WHERE (LinkPeopleToCompanies.ToDate >= GETDATE() - 30 OR LinkPeopleToCompanies.ToDate IS NULL)
   AND LinkPeopleToCompanies.SinceDate <= GETDATE() - 30
   AND LinkPeopleToCompanies.CompaniesID = Companies.CompaniesID) AS AverageDirectorAge1MonthAgo,

/我重复相同的代码，逐步回溯 30 天或 1 个月。需要一些更有效的代码/

   (SELECT AVG(LinkPeopleToCompanies.CustomInt1) FROM People WITH(NOLOCK) LEFT JOIN 
   LinkPeopleToCompanies WITH(NOLOCK) ON People.PeopleID = LinkPeopleToCompanies.PeopleID
   WHERE (LinkPeopleToCompanies.ToDate >= GETDATE() - 30 OR LinkPeopleToCompanies.ToDate IS NULL)
   AND LinkPeopleToCompanies.SinceDate <= GETDATE() - 30
   AND LinkPeopleToCompanies.CompaniesID = Companies.CompaniesID) AS AverageDirectorTenure1MonthAgo,
   TotalWomenonBoard1 = CASE WHEN ((SELECT COUNT(1) FROM People WITH(NOLOCK) INNER JOIN 
   LinkPeopleToCompanies WITH(NOLOCK) ON People.PeopleID = LinkPeopleToCompanies.PeopleID
   WHERE (LinkPeopleToCompanies.ToDate >= GETDATE()- 30 OR LinkPeopleToCompanies.ToDate IS NULL)
   AND LinkPeopleToCompanies.SinceDate <= GETDATE()- 30
   AND LinkPeopleToCompanies.CompaniesID = Companies.CompaniesID) = 0 )
   THEN '9999999'
   ELSE (SELECT COUNT(1) FROM People WITH(NOLOCK) INNER JOIN LinkPeopleToCompanies WITH(NOLOCK) ON 
   People.PeopleID = LinkPeopleToCompanies.PeopleID
   WHERE (LinkPeopleToCompanies.ToDate >= GETDATE() - 30 OR LinkPeopleToCompanies.ToDate IS NULL)
   AND LinkPeopleToCompanies.SinceDate <= GETDATE() - 30
   AND LinkPeopleToCompanies.CompaniesID = Companies.CompaniesID
   AND People.Gender = 'F')
   END,
   TotalWomenonBoard2 = CASE WHEN ((SELECT COUNT(1) FROM People WITH(NOLOCK) INNER JOIN 
   LinkPeopleToCompanies WITH(NOLOCK) ON People.PeopleID = LinkPeopleToCompanies.PeopleID
   WHERE (LinkPeopleToCompanies.ToDate >= GETDATE()- 60 OR LinkPeopleToCompanies.ToDate IS NULL)
   AND LinkPeopleToCompanies.SinceDate <= GETDATE()- 60
   AND LinkPeopleToCompanies.CompaniesID = Companies.CompaniesID) = 0 )
   THEN '9999999'
   ELSE (SELECT COUNT(1) FROM People WITH(NOLOCK) INNER JOIN LinkPeopleToCompanies WITH(NOLOCK) ON 
   People.PeopleID = LinkPeopleToCompanies.PeopleID
   WHERE (LinkPeopleToCompanies.ToDate >= GETDATE() - 60 OR LinkPeopleToCompanies.ToDate IS NULL)
   AND LinkPeopleToCompanies.SinceDate <= GETDATE() - 60
   AND LinkPeopleToCompanies.CompaniesID = Companies.CompaniesID
   AND People.Gender = 'F')
   END,
   TotalWomenonBoard60 = CASE WHEN ((SELECT COUNT(1) FROM People WITH(NOLOCK) INNER JOIN 
   LinkPeopleToCompanies WITH(NOLOCK) ON People.PeopleID = LinkPeopleToCompanies.PeopleID
   WHERE (LinkPeopleToCompanies.ToDate >= GETDATE()- 1825 OR LinkPeopleToCompanies.ToDate IS NULL)
   AND LinkPeopleToCompanies.SinceDate <= GETDATE()- 1825
   AND LinkPeopleToCompanies.CompaniesID = Companies.CompaniesID) = 0 )
   THEN '9999999'
   ELSE (SELECT COUNT(1) FROM People WITH(NOLOCK) INNER JOIN LinkPeopleToCompanies WITH(NOLOCK) ON 
   People.PeopleID = LinkPeopleToCompanies.PeopleID
   WHERE (LinkPeopleToCompanies.ToDate >= GETDATE()- 1825 OR LinkPeopleToCompanies.ToDate IS NULL)
   AND LinkPeopleToCompanies.SinceDate <= GETDATE()- 1825
   AND LinkPeopleToCompanies.CompaniesID = Companies.CompaniesID
   AND People.Gender = 'F')
   END
   FROM Companies WITH(NOLOCK)
   ORDER BY Company`

Answer 1

问题中的语法似乎是 Transact-SQL ala Microsoft SQL Server(与 MySQL 和 Oracle 不同；这个问题似乎与实际的客户端程序无关，该细节可能很重要......先将其放在一边。

<小时/>

为了性能，我会选择使用条件聚合。我将使用单个 SELECT 和一组表引用，并避免对具有不同 WHERE< 的相同表进行多个 SELECT 查询 子句。

要获取“某些”行的计数，我会在表达式中使用条件来返回 1 或 0，然后使用 SUM 将它们相加。 (或者，我返回非 null 和 NULL，并使用 COUNT。)

如果不深入研究问题中包含的每个SELECT ... FROM，看起来我们需要的唯一语句形式是这样的:

SELECT GETDATE() AS CurrentDate

     , SUM(CASE WHEN ... THEN 1 ELSE 0 END) AS stat_count_foo

  FROM Companies c
  LEFT
  JOIN LinkPeopleToCompanies t
    ON t.companiesid = c.companiesid
  LEFT
  JOIN People p
    ON p.peopleid = t.peopleid
 GROUP
    BY c.companiesid

请注意，其中没有 WHERE 子句。 (我们只会在 WHERE 子句中包含条件，以排除任何指标不需要的行。)

添加一些示例指标，我们的语句将如下所示:

SELECT GETDATE() AS CurrentDate

     , SUM( CASE
              WHEN (t.todate >= GETDATE() OR t.todate IS NULL) 
                AND t.sincedate <= GETDATE() - 2190
              THEN 1
              ELSE 0
            END
       ) AS TotalDirectorsAddedin6years

      , AVG( CASE
               WHEN (t.todate >= GETDATE() - 30 OR t.todate IS NULL) 
                 AND t.sincedate <= GETDATE() - 30
               THEN DATEDIFF('yyyy',p.birthDay,GETDATE())
               ELSE NULL
             END
        ) AS AverageDirectorAge1MonthAgo

      , SUM( CASE 
               WHEN (t.todate >= GETDATE()- 1825 OR t.todate IS NULL) 
                 AND t.sincedate <= GETDATE()- 1825
                 AND p.Gender = 'F'
               THEN 1
               ELSE 0
             END
         ) AS  TotalWomenonBoard60

  FROM Companies c
  LEFT
  JOIN LinkPeopleToCompanies t
    ON t.companiesid = c.companiesid
  LEFT
  JOIN People p
    ON p.peopleid = t.peopleid
 GROUP
    BY c.companiesid

通过添加其他表达式来扩展此功能。请勿添加任何其他 SELECT、FROM 或 JOIN 关键字。我们可以将聚合表达式包装在另一个表达式中，以用“0”替换 NULL 值(对于没有任何人员链接的公司。