在SQL Server中为呼叫计算不同的资费期限 [英] Calculating different tariff-periods for a call in SQL Server

问题描述

对于通话费率系统，我正在尝试将通话时间分为不同费率时段的子时间段。这些调用存储在SQL Server数据库中，并且具有开始时间和总持续时间。晚上（0000-0800），高峰（0800-1900）和非高峰（1900-235959）时段的价格有所不同。

For a call-rating system, I'm trying to split a telephone call duration into sub-durations for different tariff-periods. The calls are stored in a SQL Server database and have a starttime and total duration. Rates are different for night (0000 - 0800), peak (0800 - 1900) and offpeak (1900-235959) periods.

例如：
呼叫始于18:50:00，持续时间为1000秒。这将使呼叫在19:06:40结束，从而使峰值费率达到10分钟/ 600秒，而非高峰费率达到400秒。

For example: A call starts at 18:50:00 and has a duration of 1000 seconds. This would make the call end at 19:06:40, making it 10 minutes / 600 seconds in the peak-tariff and 400 seconds in the off-peak tariff.

很明显，通话可以无限期结束（我们不规定最长通话时间）。通话时间> 24小时，可以涵盖从高峰期开始，非高峰期，夜间和返回高峰期的所有三个时段。

Obviously, a call can wrap over an unlimited number of periods (we do not enforce a maximum call duration). A call lasting > 24 h can wrap all 3 periods, starting in peak, going through off-peak, night and back into peak tariff.

当前，我们正在使用VB中的递归计算不同的关税期限。我们计算在相同的资费期限内有多少呼叫进入通话，相应地更改呼叫的开始时间和持续时间，并重复此过程，直到达到通话的整个持续时间为止（peakDuration + offpeakDuration + nightDuration == callDuration）。

Currently, we are calculating the different tariff-periods using recursion in VB. We calculate how much of the call goes in the same tariff-period the call starts in, change the starttime and duration of the call accordingly and repeat this process till the full duration of the call has been reach (peakDuration + offpeakDuration + nightDuration == callDuration).

关于此问题，我有2个问题：

Regarding this issue, I have 2 questions:

• 是是否可以在SQL Server语句中有效执行此操作？ （我可以想到存储过程中的子查询或大量编码，但这不会产生任何性能改进）

• Is it possible to do this effectively in a SQL Server statement? (I can think of subqueries or lots of coding in stored procedures, but that would not generate any performance improvement)

SQL Server将能够进行此类计算比当前的VB脚本更节省资源？

Will SQL Server be able to do such calculations in a way more resource-effective than the current VB scripts are doing it?

推荐答案

在我看来，这是一个分为两个阶段的操作。

It seems to me that this is an operation with two phases.

1. 确定电话的哪个部分使用哪个费率时间。


2. 每个费率的时间总和。

第一阶段比阶段2。因为没有MS SQL Server，所以我在IBM Informix Dynamic Server（IDS）中处理了该示例。这些想法应该足够容易地翻译。 INTO TEMP子句使用适当的模式创建一个临时表；该表是会话专用的，并在会话结束时消失（或显式删除它）。在IDS中，您还可以使用显式的CREATE TEMP TABLE语句，然后使用INSERT INTO临时表SELECT ...作为执行INTO TEMP相同工作的更详细的方法。

Phase 1 is trickier than Phase 2. I've worked the example in IBM Informix Dynamic Server (IDS) because I don't have MS SQL Server. The ideas should translate easily enough. The INTO TEMP clause creates a temporary table with an appropriate schema; the table is private to the session and vanishes when the session ends (or you explicitly drop it). In IDS, you can also use an explicit CREATE TEMP TABLE statement and then INSERT INTO temp-table SELECT ... as a more verbose way of doing the same job as INTO TEMP.

就像在SO上的SQL问题中一样，您没有为我们提供模式，因此每个人都必须发明一个可能匹配或可能不匹配您描述的模式。

As so often in SQL questions on SO, you've not provided us with a schema, so everyone has to invent a schema that might, or might not, match what you describe.

让我们假设您的数据在两个表中。第一个表包含通话记录记录，有关通话的基本信息，例如拨打电话，所拨打的电话号码，通话开始的时间以及通话的持续时间：

Let's assume your data is in two tables. The first table has the call log records, the basic information about the calls made, such as the phone making the call, the number called, the time when the call started and the duration of the call:

CREATE TABLE clr  -- call log record
(
    phone_id      VARCHAR(24) NOT NULL,   -- billing plan
    called_number VARCHAR(24) NOT NULL,   -- needed to validate call
    start_time    TIMESTAMP   NOT NULL,   -- date and time when call started
    duration      INTEGER     NOT NULL    -- duration of call in seconds
                  CHECK(duration > 0),
    PRIMARY KEY(phone_id, start_time)
    -- other complicated range-based constraints omitted!
    -- foreign keys omitted
    -- there would probably be an auto-generated number here too.
);
INSERT INTO clr(phone_id, called_number, start_time, duration)
    VALUES('650-656-3180', '650-794-3714', '2009-02-26 15:17:19', 186234);

为了方便起见（主要是为了节省多次写入操作），我想要一个clr表的副本实际结束时间：

For convenience (mainly to save writing the addition multiple times), I want a copy of the clr table with the actual end time:

SELECT  phone_id, called_number, start_time AS call_start, duration,
        start_time + duration UNITS SECOND AS call_end
    FROM clr
    INTO TEMP clr_end;

关税数据存储在一个简单的表中：

The tariff data is stored in a simple table:

CREATE TABLE tariff
(
    tariff_code   CHAR(1)      NOT NULL   -- code for the tariff
                  CHECK(tariff_code IN ('P','N','O'))
                  PRIMARY KEY,
    rate_start    TIME         NOT NULL,  -- time when rate starts
    rate_end      TIME         NOT NULL,  -- time when rate ends
    rate_charged  DECIMAL(7,4) NOT NULL   -- rate charged (cents per second)
);
INSERT INTO tariff(tariff_code, rate_start, rate_end, rate_charged)
    VALUES('N', '00:00:00', '08:00:00', 0.9876);
INSERT INTO tariff(tariff_code, rate_start, rate_end, rate_charged)
    VALUES('P', '08:00:00', '19:00:00', 2.3456);
INSERT INTO tariff(tariff_code, rate_start, rate_end, rate_charged)
    VALUES('O', '19:00:00', '23:59:59', 1.2345);

我辩论了关税表应使用TIME还是INTERVAL值；在这种情况下，时间与相对于午夜的时间间隔非常相似，但是可以将时间间隔添加到时间不能做到的时间戳上。我坚持使用时间，但它使事情变得混乱。

I debated whether the tariff table should use TIME or INTERVAL values; in this context, the times are very similar to intervals relative to midnight, but intervals can be added to timestamps where times cannot. I stuck with TIME, but it made things messy.

此查询的棘手部分是为每个费率生成相关的日期和时间范围而没有循环。实际上，我最终使用了存储过程中嵌入的循环来生成整数列表。 （我还使用了特定于IBM Informix Dynamic Server IDS的技术，它使用系统目录中的表ID号作为范围为1..N的连续整数的来源，该版本在版本1到60之间适用11.50。）

The tricky part of this query is generating the relevant date and time ranges for each tariff without loops. In fact, I ended up using a loop embedded in a stored procedure to generate a list of integers. (I also used a technique that is specific to IBM Informix Dynamic Server, IDS, using the table ID numbers from the system catalog as a source of contiguous integers in the range 1..N, which works for numbers from 1 to 60 in version 11.50.)

CREATE PROCEDURE integers(lo INTEGER DEFAULT 0, hi INTEGER DEFAULT 0)
    RETURNING INT AS number;
    DEFINE i INTEGER;
    FOR i = lo TO hi STEP 1
        RETURN i WITH RESUME;
    END FOR;
END PROCEDURE;

在最简单的情况下（也是最常见的情况），通话属于单一关税期;多时段通话增加了兴奋感。

In the simple case (and the most common case), the call falls in a single-tariff period; the multi-period calls add the excitement.

假设我们可以创建一个与该模式匹配的表表达式，并覆盖我们可能需要的所有时间戳值：

Let's assume we can create a table expression that matches this schema and covers all the timestamp values we might need:

CREATE TEMP TABLE tariff_date_time
(
     tariff_code   CHAR(1)      NOT NULL,
     rate_start    TIMESTAMP    NOT NULL,
     rate_end      TIMESTAMP    NOT NULL,
     rate_charged  DECIMAL(7,4) NOT NULL
);

幸运的是，您没有提到周末价格，所以您向客户收取相同的费用

Fortunately, you haven't mentioned weekend rates, so you charge the customers the same

在周末和一周进行收费。但是，答案应尽可能适应

rates at the weekend as during the week. However, the answer should adapt to such

的情况。如果您要像

公共假期的周末费率一样复杂，除了圣诞节或新年，则要收取峰值费率，而不是

public holidays, except that at Christmas or New Year, you charge peak rate instead of

由于高需求的周末费率，那么最好将费率存储在永久性的date_date_time表中。

weekend rate because of the high demand, then you would be best off storing the rates in a permanent tariff_date_time table.

第一步在填充riffit_date_time中将生成与呼叫相关的日期列表：

The first step in populating tariff_date_time is to generate a list of dates which are relevant to the calls:

SELECT DISTINCT EXTEND(DATE(call_start) + number, YEAR TO SECOND) AS call_date
    FROM clr_end,
         TABLE(integers(0, (SELECT DATE(call_end) - DATE(call_start) FROM clr_end)))
         AS date_list(number)
    INTO TEMP call_dates;

两个日期值之间的差是整数天数（在IDS中）。
过程 integers 生成从0到调用覆盖的天数的值，并将结果存储在临时表中。对于具有多个记录的更一般的情况，最好计算最小和最大日期并生成中间的日期，而不是多次生成日期，然后使用DISTINCT子句消除它们。

The difference between the two date values is an integer number of days (in IDS). The procedure integers generates values from 0 to the number of days covered by the call and stores the result in a temp table. For the more general case of multiple records, it might be better to calculate the minimum and maximum dates and generate the dates in between rather than generate dates multiple times and then eliminate them with the DISTINCT clause.

现在使用费率表的笛卡尔乘积与call_dates表来生成每天的费率信息。

Now use a cartesian product of the tariff table with the call_dates table to generate the rate information for each day. This is where the tariff times would be neater as intervals.

SELECT  r.tariff_code,
        d.call_date + (r.rate_start - TIME '00:00:00') AS rate_start,
        d.call_date + (r.rate_end   - TIME '00:00:00') AS rate_end,
        r.rate_charged
    FROM call_dates AS d, tariff AS r
    INTO TEMP tariff_date_time;

现在我们需要将通话记录与所适用的资费相匹配。此条件是处理重叠的一种标准方法-如果第一个时间段的末尾晚于第二个时间段的末尾，并且第一个时间段的开始时间在第二个时间段的末尾之前，则两个时间段重叠：

Now we need to match the call log record with the tariffs that apply. The condition is a standard way of dealing with overlaps - two time periods overlap if the end of the first is later than the start of the second and if the start of the first is before the end of the second:

SELECT tdt.*, clr_end.*
FROM tariff_date_time tdt, clr_end
WHERE tdt.rate_end > clr_end.call_start
  AND tdt.rate_start < clr_end.call_end
INTO TEMP call_time_tariff;

然后，我们需要确定费率的开始和结束时间。费率的开始时间是费率的开始时间和呼叫开始时间中的较晚时间。费率的结束时间是费率的结束时间和呼叫的结束时间中的较早时间：

Then we need to establish the start and end times for the rate. The start time for the rate is the later of the start time for the tariff and the start time of the call. The end time for the rate is the earlier of the end time for the tariff and the end time of the call:

SELECT  phone_id, called_number, tariff_code, rate_charged,
        call_start, duration,
        CASE WHEN rate_start < call_start THEN call_start
        ELSE rate_start END AS rate_start,
        CASE WHEN rate_end >= call_end THEN call_end
        ELSE rate_end END AS rate_end
    FROM call_time_tariff
    INTO TEMP call_time_tariff_times;

最后，我们需要将每个费率所花费的时间相加，并花费该时间（以秒为单位） ）乘以收费率。由于SUM（rate_end-rate_start）的结果是一个INTERVAL，而不是数字，因此我必须调用转换函数将INTERVAL转换为DECIMAL秒数，并且该（非标准）函数为iv_seconds：

Finally, we need to sum the times spent at each tariff rate, and take that time (in seconds) and multiply by the rate charged. Since the result of SUM(rate_end - rate_start) is an INTERVAL, not a number, I had to invoke a conversion function to convert the INTERVAL into a DECIMAL number of seconds, and that (non-standard) function is iv_seconds:

SELECT phone_id, called_number, tariff_code, rate_charged,
       call_start, duration,
       SUM(rate_end - rate_start) AS tariff_time,
       rate_charged * iv_seconds(SUM(rate_end - rate_start)) AS tariff_cost
   FROM call_time_tariff_times
   GROUP BY phone_id, called_number, tariff_code, rate_charged,
            call_start, duration;

对于示例数据，这产生了数据（在这里我不打印电话号码并打电话给紧凑性数）：

For the sample data, this yielded the data (where I'm not printing the phone number and called number for compactness):

N   0.9876   2009-02-26 15:17:19   186234   0 16:00:00   56885.760000000
O   1.2345   2009-02-26 15:17:19   186234   0 10:01:11   44529.649500000
P   2.3456   2009-02-26 15:17:19   186234   1 01:42:41  217111.081600000

这是一个非常昂贵的电话，但电信公司会对此感到满意。您可以戳任何中间结果，以查看得出答案的方式。您可以使用较少的临时表，但要花些时间。

That's a very expensive call, but the telco will be happy with that. You can poke at any of the intermediate results to see how the answer is derived. You can use fewer temporary tables at the cost of some clarity.

对于单个调用，这与在客户端的VB中运行代码没有太大区别。对于许多呼叫，这有可能变得更有效率。我远未确信VB中必须进行递归-直接迭代就足够了。

For a single call, this will not be much different than running the code in VB in the client. For a lot of calls, this has the potential to be more efficient. I'm far from convinced that recursion is necessary in VB - straight iteration should be sufficient.

这篇关于在SQL Server中为呼叫计算不同的资费期限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！