SQL-识别成绩下降的公司

Question

我正在使用CompaniD，Score和Timestamp的表工作。

运行选择会产生客户列表及其不断变化的分数，这是一个限制在一个公司的示例：

：

https://i.stack.imgur.com/f09ad.png

我想制作所有公司的列表，其分数在两个最近的时间戳上下降了x％，但是我对SQL的新时间却相对较新，并且不确定如何实现这一目标。任何帮助将不胜感激。

Answer 1

SELECT * FROM (
SELECT companyProfileId, engagementScore, row_number() OVER(PARTITION BY companyProfileId ORDER BY createTimeStamp Desc) AS rowNum
FROM _yourTable_) AS foo
WHERE foo.rowNum() <= 2
AND engagementScore * X = y

根据MSSQL条款，希望您正在使用MSSQL，因为此RDBMS的

Answer 2

这是在MS SQL和MySQL中测试的替代解决方案。我在http://sqlfiddle.com上进行了测试。

这是查询。它可能不是最有效的，但相当简单。

SELECT
    EN3.companyProfileId
  , EN3.maxCreateTimestamp
  , EN3.secondMaxCreateTimestamp
  , EN4.engagementScore newestEngagementScore
  , EN5.engagementScore secondNewestEngagementScore
  , EN4.engagementScore - EN5.engagementScore AS engagementDifference
  , 100.0 * (EN4.engagementScore - EN5.engagementScore) / EN5.engagementScore percentDifference
FROM
(
    SELECT
        EN1.companyProfileId
      , EN2.maxCreateTimestamp
      , MAX(EN1.createTimestamp) secondMaxCreateTimestamp
    FROM
        engagement EN1
    INNER JOIN
        (
        SELECT
            companyProfileId
          , MAX(createTimestamp) maxCreateTimestamp
        FROM
            engagement
        GROUP BY
            companyProfileId
        ) EN2
    ON
        EN1.companyProfileId = EN2.companyProfileId
    AND EN1.createTimestamp != EN2.maxCreateTimestamp
    GROUP BY
        EN1.companyProfileId
      , EN2.maxCreateTimestamp
) EN3
INNER JOIN
    engagement EN4
ON
    EN3.companyProfileId = EN4.companyProfileId
AND EN3.maxCreateTimestamp = EN4.createTimestamp
INNER JOIN
    engagement EN5
ON
    EN3.companyProfileId = EN5.companyProfileId
AND EN3.secondMaxCreateTimestamp = EN5.createTimestamp
WHERE
    100.0 * (EN4.engagementScore - EN5.engagementScore) / (EN5.engagementScore) < -10.00

这是MS-SQL的测试数据。

CREATE TABLE engagement (
    companyProfileId INT
  , engagementScore INT
  , createTimestamp DATETIME
);
GO
-- Increased 5%, declined 1%, declined 11%; should be returned for 10%.
INSERT INTO engagement(companyProfileId, engagementScore, createTimestamp) 
SELECT 1000, 7500 * 1.05 * 0.99 * 0.89, DATEADD(dd, -1, GETDATE())
INSERT INTO engagement(companyProfileId, engagementScore, createTimestamp) 
SELECT 1000, 7500 * 1.05 * 0.99, DATEADD(dd, -2, GETDATE())
INSERT INTO engagement(companyProfileId, engagementScore, createTimestamp) 
SELECT 1000, 7500 * 1.05, DATEADD(dd, -3, GETDATE())
INSERT INTO engagement(companyProfileId, engagementScore, createTimestamp) 
SELECT 1000, 7500, DATEADD(dd, -4, GETDATE())
-- Only has one score; should not be returned.
INSERT INTO engagement(companyProfileId, engagementScore, createTimestamp) 
SELECT 1001, 8000, DATEADD(dd, -1, GETDATE())
-- Declined 15%, declined 15%, increased 15%; should not be returned.
INSERT INTO engagement(companyProfileId, engagementScore, createTimestamp) 
SELECT 1002, 6000 * 0.85 * 0.85 * 1.15, DATEADD(dd, -1, GETDATE())
INSERT INTO engagement(companyProfileId, engagementScore, createTimestamp) 
SELECT 1002, 6000 * 0.85 * 0.85, DATEADD(dd, -2, GETDATE())
INSERT INTO engagement(companyProfileId, engagementScore, createTimestamp) 
SELECT 1002, 6000 * 0.85, DATEADD(dd, -3, GETDATE())
-- Increased 5%, declined 1%, declined 11%; should be returned for 10%.
INSERT INTO engagement(companyProfileId, engagementScore, createTimestamp) 
SELECT 1003, 5000 * 1.05 * 0.89, DATEADD(dd, -1, GETDATE())
INSERT INTO engagement(companyProfileId, engagementScore, createTimestamp) 
SELECT 1003, 5000 * 1.05, DATEADD(dd, -2, GETDATE())
-- Declined 9%, declined 9%; should not be returned for 10%.
INSERT INTO engagement(companyProfileId, engagementScore, createTimestamp) 
SELECT 1004, 9250 * 0.91 * 0.91, DATEADD(dd, -1, GETDATE())
INSERT INTO engagement(companyProfileId, engagementScore, createTimestamp) 
SELECT 1004, 9250 * 0.91, DATEADD(dd, -2, GETDATE())
GO

测试数据的MySQL版本非常相似，因此我省略了其中的大部分，但它的形式是：

INSERT INTO engagement(companyProfileId, engagementScore, createTimestamp) 
SELECT 1002, 7500 * 0.85 * 0.85 * 1.15, DATE_ADD(CURDATE(), INTERVAL -1 DAY);

Answer 3

您可以尝试一下，使用窗口函数可以解决您的问题...

SELECT z.companyProfileId,z.engagementScore,z.dropBy,z.createTimestamp FROM (
    SELECT a.companyProfileId,a.engagementScore,CONVERT(DECIMAL(10,2),(CONVERT(DECIMAL,(a.lastScore-a.engagementScore))/a.lastScore)*100) dropBy, a.createTimestamp,RANK() OVER (PARTITION BY a.companyProfileId ORDER BY a.createTimestamp desc) AS [rank]
    FROM   (
               SELECT a.companyProfileId,
                      a.engagementScore,
                      a.engagementScore -Lag(a.engagementScore, 1) OVER(PARTITION BY a.companyProfileId ORDER BY a.createTimestamp) AS 
                      differ,
                      Lag(a.engagementScore, 1) OVER(PARTITION BY a.companyProfileId ORDER BY a.createTimestamp) AS lastScore,
                      a.createTimestamp
               FROM   engagement a
           ) a
    WHERE  a.differ < 0
    ) z WHERE [rank]=1