如何使用 SQL 计算通话费用并写入新表
支付阿杰
可以使用 INSERT ... SELECT 把计费结果写入新表。匹配跨午夜的 tariff 时，不能只用 BETWEEN，需要分别处理普通时间段和 ratestart > rateend 的跨午夜时间段。
计费一般使用 billsec，而不是 duration，因为 billsec 通常表示通话接通后的实际计费秒数。同时需要确定 ratecharged 表示每秒价格、每分钟价格，还是每个开始分钟的价格。计价方式不明确，SQL 就无法正确计算费用。
完善 tariff 表
tariff 表可以保存名称、适用日期类型、计费单位和舍入方式：
CREATE TABLE tariffs (
tariff_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
tariff_name VARCHAR(50) NOT NULL,
day_type ENUM('WEEKDAY', 'WEEKEND') NOT NULL,
ratestart TIME NOT NULL,
rateend TIME NOT NULL,
billing_unit_seconds SMALLINT UNSIGNED NOT NULL,
charge_mode ENUM('PRORATED', 'STARTED_UNIT') NOT NULL,
ratecharged DECIMAL(18,8) NOT NULL
);假设题目中的价格是每分钟价格，可以这样写：
INSERT INTO tariffs
(tariff_name, day_type, ratestart, rateend,
billing_unit_seconds, charge_mode, ratecharged)
VALUES
('weekday_peak', 'WEEKDAY', '08:00:00', '19:00:00',
60, 'PRORATED', 3.3333),
('weekday_off_peak', 'WEEKDAY', '19:00:00', '08:00:00',
60, 'PRORATED', 1.5555),
('weekend_off_peak', 'WEEKEND', '00:00:00', '00:00:00',
60, 'PRORATED', 1.5555);以上示例采用以下约定：
ratestart = rateend表示全天有效。ratestart < rateend表示当天的普通时间段。ratestart > rateend表示跨午夜时间段。PRORATED表示按实际秒数折算。STARTED_UNIT表示不足一个计费单位也按一个完整单位收费。
如果费率按秒计算，把 billing_unit_seconds 设置为 1 即可。
创建通话费用结果表
结果表应尽量保留原通话记录的完整唯一标识。只保存 clid，不足以唯一识别一条通话：
CREATE TABLE call_charges (
charge_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
callerid VARCHAR(32) NOT NULL,
source_number VARCHAR(64),
destination_number VARCHAR(64),
source_channel VARCHAR(32) NOT NULL,
calldate TIMESTAMP NOT NULL,
call_duration INT UNSIGNED NOT NULL,
tariff_id BIGINT UNSIGNED NOT NULL,
tariff_name VARCHAR(50) NOT NULL,
ratecharged DECIMAL(18,8) NOT NULL,
billing_unit_seconds SMALLINT UNSIGNED NOT NULL,
cost DECIMAL(18,8) NOT NULL,
UNIQUE KEY uq_cdr_charge (callerid, source_channel, calldate),
FOREIGN KEY (tariff_id) REFERENCES tariffs(tariff_id)
);在结果表中保存 ratecharged、计费单位和 tariff 名称，相当于保留一份费率快照。即使以后 tariff 表发生变化，已经出账的历史费用仍有原始计费依据。
按通话开始时间选择 tariff
如果业务规定一通电话全部按接通时的费率计费，可以直接执行：
INSERT INTO call_charges (
callerid,
source_number,
destination_number,
source_channel,
calldate,
call_duration,
tariff_id,
tariff_name,
ratecharged,
billing_unit_seconds,
cost
)
SELECT
c.clid,
c.src,
c.dst,
c.channel,
c.calldate,
c.billsec,
t.tariff_id,
t.tariff_name,
t.ratecharged,
t.billing_unit_seconds,
ROUND(
CASE t.charge_mode
WHEN 'PRORATED' THEN
c.billsec * t.ratecharged / t.billing_unit_seconds
WHEN 'STARTED_UNIT' THEN
CEIL(c.billsec / t.billing_unit_seconds) * t.ratecharged
END,
8
) AS cost
FROM cdr AS c
JOIN tariffs AS t
ON t.day_type =
CASE
WHEN WEEKDAY(c.answerdate) IN (5, 6) THEN 'WEEKEND'
ELSE 'WEEKDAY'
END
AND (
t.ratestart = t.rateend
OR (
t.ratestart < t.rateend
AND TIME(c.answerdate) >= t.ratestart
AND TIME(c.answerdate) < t.rateend
)
OR (
t.ratestart > t.rateend
AND (
TIME(c.answerdate) >= t.ratestart
OR TIME(c.answerdate) < t.rateend
)
)
)
WHERE c.billsec > 0
AND c.disposition = 'ANSWERED';MySQL 的 WEEKDAY() 返回值如下：
0：星期一4：星期五5：星期六6：星期日
时间区间采用左闭右开形式，例如 08:00:00 <= time < 19:00:00。这样可以避免时间恰好为 19:00:00 时同时匹配两条 tariff。
跨午夜的非高峰时段不能写成：
TIME(c.answerdate) BETWEEN '19:00:00' AND '08:00:00'因为它的开始时间晚于结束时间。正确条件是：
TIME(c.answerdate) >= '19:00:00'
OR TIME(c.answerdate) < '08:00:00'每秒和每分钟计费公式
假设价格为 ratecharged，计费单位秒数为 billing_unit_seconds。
按实际通话时长折算：
billsec * ratecharged / billing_unit_seconds例如每分钟价格为 3.3333，通话 30 秒：
30 × 3.3333 ÷ 60 = 1.66665
按每个开始分钟收费：
CEIL(billsec / billing_unit_seconds) * ratecharged同样是 30 秒通话：
CEIL(30 ÷ 60) × 3.3333 = 3.3333
如果 ratecharged 本身就是每秒价格，公式为：
billsec * ratecharged具体采用哪一种公式，要以实际资费合同为准。
通话跨越费率边界时的处理
前面的查询只根据接通时间选择 tariff。比如一通电话从 18:59 持续到 19:05，整通电话都会按高峰费率计费。
如果计费规则要求：
18:59到19:00按高峰收费；19:00到19:05按非高峰收费；
就需要拆分通话。此时一条通话可能对应多条计费明细，因此不适合只在 call_charges 中保存一个 tariff。
可以建立具体的费率窗口表：
CREATE TABLE tariff_windows (
tariff_window_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
tariff_id BIGINT UNSIGNED NOT NULL,
window_start DATETIME NOT NULL,
window_end DATETIME NOT NULL,
FOREIGN KEY (tariff_id) REFERENCES tariffs(tariff_id),
INDEX ix_tariff_window (window_start, window_end)
);系统可以提前生成每天的费率窗口，例如：
2026-09-01 00:00:00 至 2026-09-01 08:00:00 非高峰
2026-09-01 08:00:00 至 2026-09-01 19:00:00 高峰
2026-09-01 19:00:00 至 2026-09-02 00:00:00 非高峰
星期六和星期日只生成一个全天非高峰窗口。
计费明细表可以这样设计：
CREATE TABLE call_charge_items (
item_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
callerid VARCHAR(32) NOT NULL,
source_channel VARCHAR(32) NOT NULL,
calldate TIMESTAMP NOT NULL,
segment_start DATETIME NOT NULL,
segment_end DATETIME NOT NULL,
segment_seconds INT UNSIGNED NOT NULL,
tariff_id BIGINT UNSIGNED NOT NULL,
ratecharged DECIMAL(18,8) NOT NULL,
cost DECIMAL(18,8) NOT NULL,
FOREIGN KEY (tariff_id) REFERENCES tariffs(tariff_id)
);然后按照通话与费率窗口的交集进行拆分：
INSERT INTO call_charge_items (
callerid,
source_channel,
calldate,
segment_start,
segment_end,
segment_seconds,
tariff_id,
ratecharged,
cost
)
SELECT
x.clid,
x.channel,
x.calldate,
x.segment_start,
x.segment_end,
TIMESTAMPDIFF(
SECOND,
x.segment_start,
x.segment_end
) AS segment_seconds,
x.tariff_id,
x.ratecharged,
ROUND(
CASE x.charge_mode
WHEN 'PRORATED' THEN
TIMESTAMPDIFF(
SECOND,
x.segment_start,
x.segment_end
) * x.ratecharged / x.billing_unit_seconds
WHEN 'STARTED_UNIT' THEN
CEIL(
TIMESTAMPDIFF(
SECOND,
x.segment_start,
x.segment_end
) / x.billing_unit_seconds
) * x.ratecharged
END,
8
) AS cost
FROM (
SELECT
c.clid,
c.channel,
c.calldate,
GREATEST(c.answerdate, w.window_start) AS segment_start,
LEAST(
DATE_ADD(c.answerdate, INTERVAL c.billsec SECOND),
w.window_end
) AS segment_end,
t.tariff_id,
t.ratecharged,
t.billing_unit_seconds,
t.charge_mode
FROM cdr AS c
JOIN tariff_windows AS w
ON w.window_start <
DATE_ADD(c.answerdate, INTERVAL c.billsec SECOND)
AND w.window_end > c.answerdate
JOIN tariffs AS t
ON t.tariff_id = w.tariff_id
WHERE c.billsec > 0
AND c.disposition = 'ANSWERED'
) AS x
WHERE x.segment_end > x.segment_start;每通电话的总费用可以进一步聚合：
SELECT
callerid,
source_channel,
calldate,
SUM(segment_seconds) AS billed_seconds,
SUM(cost) AS total_cost
FROM call_charge_items
GROUP BY callerid, source_channel, calldate;如果采用“每个开始分钟收费”，还需要确定跨 tariff 拆分后，是每段分别向上取整，还是整通电话只取整一次。这两种规则算出的费用可能不同，必须按照业务合同确定。
是否需要用户表
计费计算本身不要求必须有用户表。如果还要维护客户姓名、套餐、账户状态或账单归属，可以单独建表：
CREATE TABLE users (
accountcode VARCHAR(30) PRIMARY KEY,
display_name VARCHAR(100) NOT NULL,
status ENUM('ACTIVE', 'SUSPENDED', 'CLOSED') NOT NULL
);再通过 cdr.accountcode 关联用户：
SELECT
u.display_name,
c.src,
c.dst,
c.billsec
FROM cdr AS c
JOIN users AS u
ON u.accountcode = c.accountcode;不要把 clid 作为稳定的用户主键，因为它可能包含显示名称、号码或不同格式。accountcode 通常更适合作为计费账户标识，但仍需结合实际的 CDR 生成规则确认。
注意事项
- 确保同一时刻最多匹配一条 tariff，否则
JOIN会重复生成费用。 - 对已接通的电话，优先使用
answerdate和billsec。 - 费用字段应使用
DECIMAL，不要使用FLOAT或DOUBLE。 - 明确费率是否含税，以及最终在哪一级进行金额舍入。
- 周末、节假日和特殊促销费率可以作为独立规则维护。
- 批量任务应通过唯一键保证幂等，避免重复运行后生成重复账单。
- 如果资费还会随号码前缀、国家、运营商或用户套餐变化，需要增加 destination pattern、tariff plan 和生效日期等字段。
备注：内容仅供参考。