sql-server - 将两个sql表合并在一起的最佳方法

Question

所以我有两个表。一个跟踪一个人的位置，一个跟踪工作人员的变化。

工作人员有一个 staffId、位置、开始和结束时间，以及轮类的成本。

人们有一个 eventId、stayId、personId、位置、开始和结束时间。一个人会有多次停留的事件。

我试图做的是将这两个表格结合在一起，这样我就可以准确地报告每个地点停留的费用，计算依据是停留的持续时间乘以当时负责该地点的员工的相关成本。

我遇到的问题是:

1. 位置停留与员工轮类不一致。例如，一个人可能在下午 1 点到 2 点之间在位置 a，四个员工可能在 12:30 到 1:30 轮类，两个人在 1:30 到 5 点轮类。
2. 有很多记录。
3. 并非所有员工的薪酬都相同

我目前的方法是将这两个表扩展为每一分钟都有一条记录。因此，下午 1 点到 2 点之间的逗留将有 60 条记录，而持续 5 小时的员工轮类将有 300 条记录。然后，我可以根据每个员工的成本除以他们轮类的持续时间，让当时在该地点工作的所有员工获得一个分钟值，并将该值应用于另一个表中的相应记录。

使用的技术:

1. 我创建了一个包含 50,000 个数字的表格，因为有些住宿可能相当长。
2. 我将职员表加入到数字表中以拆分每个表转移。然后根据位置和分钟将其组合在一起，并使用员 worker 数和分钟成本。
3. 最后一步，也是导致问题的一步，我采取了位置表，连接到数字，也连接到修改后的员工表产生那一分钟的成本。我也数数该地点的人员占多个人。

我发现这个过程非常慢，正如你想象的那样，因为我的人员表在扩展到分钟级别时有大约 5 亿条记录，而当同样的事情完成时，员工表有大约 3500 万条记录。

有人可以建议我使用更好的方法吗？

示例数据:地点

| EventId |  ID | Person | Loc |          Start         |         End
|  1      | 987 |  123   |  1  | May, 20 2015 07:00:00 | May, 20 2015 08:00:00 
|  1      | 374 |  123   |  4  | May, 20 2015 08:00:00 | May, 20 2015 10:00:00 
|  1      | 184 |  123   |  3  | May, 20 2015 10:00:00 | May, 20 2015 11:00:00 
|  1      | 798 |  123   |  8  | May, 20 2015 11:00:00 | May, 20 2015 12:00:00 

工作人员

| Loc | StaffID | Cost |         Start         |         End
|  1  | 99      |  40  | May, 20 2015 04:00:00 | May, 20 2015 12:00:00 
|  1  | 15      |  85  | May, 20 2015 03:00:00 | May, 20 2015 5:00:00 
|  3  | 85      |  74  | May, 20 2015 18:00:00 | May, 20 2015 20:00:00 
|  4  | 10      |  36  | May, 20 2015 06:00:00 | May, 20 2015 14:00:00 

结果

|事件编号 |编号 |人 |地点 |开始|结束 |成本
| 1 | 987 | 123 | 1 | 2015 年 5 月 20 日 07:00:00 | 2015 年 5 月 20 日 08:00:00 | 45.50
| 1 | 374 | 123 | 4 | 2015 年 5 月 20 日 08:00:00 | 2015 年 5 月 20 日 10:00:00 | 81.20
| 1 | 184 | 123 | 3 | 2015 年 5 月 20 日 10:00:00 | 2015 年 5 月 20 日 11:00:00 | 95.00
| 1 | 798| 123 | 8 | 2015 年 5 月 20 日 11:00:00 | 2015 年 5 月 20 日 12:00:00 | 14.75

查询语句:数字表

;WITH x AS 
(
  SELECT TOP (224) object_id  FROM sys.all_objects 
)
SELECT TOP (50000) n = ROW_NUMBER() OVER (ORDER BY x.object_id) 
INTO #numbers
FROM x CROSS JOIN x AS y 
ORDER BY n

职员表

SELECT 
    Location,
    ISNULL(SUM(ROUND(Cost/ CASE WHEN (DateDiff(MINUTE, StartDateTime, EndDateTime)) = 0 THEN 1 ELSE (DateDiff(MINUTE, StartDateTime, EndDateTime)) END, 5)),0) AS MinuteCost,
    Count(Name) AS StaffCount,
    RosterMinute = DATEADD(MI, DATEDIFF(MI, 0, StartDateTime) + n.n -1,     0) 
INTO #temp_StaffRoster
FROM dbo.StaffRoster

聚在一起，我认为需要帮助的地方

    INSERT INTO dbo.FinalTable
    SELECT [EventId]
          ,[Id]
          ,[Start]
          ,[End]
          ,event.[Location]
          ,SUM(ISNULL(MinuteCost,1)/ISNULL(PeopleCount, 1)) AS Cost
          ,AVG(ISNULL(StaffCount,1)) AS AvgStaff
      FROM dbo.Events event WITH (NOLOCK) 
      INNER JOIN #numbers n ON n.n BETWEEN 0 AND  DATEDIFF(MINUTE, Start, End)
      LEFT OUTER JOIN #temp_StaffRoster staff WITH (NOLOCK) ON staff.Location= event.Location AND staff.RosterMinute = DATEADD(MI, DATEDIFF(MI, 0, Start) + n.n -1 ,     0)
      LEFT OUTER JOIN (SELECT [Location], DATEADD(MI, DATEDIFF(MI, 0, Start) + n.n -1 ,     0) AS Mins, COUNT(Id) as PeopleCount
                       FROM dbo.Events WITH (NOLOCK) 
                       INNER JOIN #numbers n ON n.n BETWEEN 0 AND  DATEDIFF(MINUTE, Start, End)
                       GROUP BY [Location], DATEADD(MI, DATEDIFF(MI, 0, Start) + n.n -1 ,     0)
                       ) cap ON cap.Location= event.LocationAND cap.Mins = DATEADD(MI, DATEDIFF(MI, 0, Start) + n.n -1 ,     0)

      GROUP BY [EventId]
              ,[Id]
              ,[Start]
              ,[End]
              ,event.[Location]

更新

所以我有两个表。一个跟踪一个人的位置，另一个跟踪工作人员及其成本的变化。我试图合并这两个表来计算每个位置停留的成本。

这是我的方法:

;;WITH stay AS
(
    SELECT TOP 650000
        StayId,
        Location,
        Start,
        End
    FROM stg_Stay
    WHERE Loction IS NOT NULL  -- Some locations don't currently have a matching shift location
    ORDER BY Location, ADTM
),
shift AS
(
    SELECT TOP 36000000
        Location,
        ShiftMinute,
        MinuteCost,
        StaffCount
    FROM stg_Shifts
    ORDER BY Location, ShiftMinute
)

SELECT 
    [StayId],
    SUM(MinuteCost) AS Cost,
    AVG(StaffCount) AS StaffCount
INTO newTable
FROM stay S
CROSS APPLY (SELECT MinuteCost, StaffCount
                FROM shift R 
                WHERE R.Location = S.Location
                 AND R.ShiftMinute BETWEEN S.Start AND S.End 
            ) AS Shifts
GROUP BY [StayId]

这就是我所在的位置。

我已将类次表按分钟级别拆分，因为轮类与住宿之间没有明确的对齐方式。

stg_Stay 包含的列多于此操作所需的列。 stg_Shift 如图所示。

stg_Shifts 上使用的索引:

CREATE NONCLUSTERED INDEX IX_Shifts_Loc_Min
ON dbo.stg_Shifts (Location, ShiftMinute)
INCLUDE (MinuteCost, StaffCount); 

在 stg_Stay

CREATE INDEX IX_Stay_StayId ON dbo.stg_Stay (StayId);
CREATE CLUSTERED INDEX IX_Stay_Start_End_Loc ON dbo.stg_Stay (Location,Start,End); 

由于 Shifts 有大约 3600 万条记录，而 Stays 有大约 650k 条记录，我该怎么做才能让它表现更好？

Answer 1

1. 不要按分钟分割行。
2. 如果您可以在它们之间建立快速关系，临时表可能会有所帮助。即重叠区间

---

SELECT * 
FROM Locations l
OUTER APPLY -- Assume a staff won't appear in different location in the same period of time, of course.
(
  SELECT 
    CONVERT(decimal(14,2), SUM(CostPerMinute * OverlappedMinutes)) AS ActualCost,
    COUNT(DISTINCT StaffId) AS StaffCount,
    SUM(OverlappedMinutes) AS StaffMinutes
  FROM
  (
    SELECT 
      *,
      -- Calculate overlapped time in minutes
      DATEDIFF(MINUTE,
        CASE WHEN StartTime > l.StartTime THEN StartTime ELSE l.StartTime END, -- Get greatest start time
        CASE WHEN EndTime > l.EndTime THEN l.EndTime ELSE EndTime END -- Get least end time
      ) AS OverlappedMinutes,
      Cost / DATEDIFF(MINUTE, StartTime, EndTime) AS CostPerMinute
    FROM Staff 
    WHERE LocationId = l.LocationId 
      AND StartTime <= l.EndTime AND l.StartTime <= EndTime -- Match with overlapped time
  ) data
) StaffInLoc

SQL Fiddle