时间戳

首页 > 解决方案 > 获取计算列的总和

sql - 获取计算列的总和

问题描述

目前我有一张桌子，上面有遛狗者的名字，他们正在遛的狗，以及他们在每个特定会话中遛狗的时间。

这是它的样子：

SELECT WALKERS.LastName ||', '|| WALKERS.FirstName AS WalkerName, DOGS.Name 
AS DogName,
(trunc( extract(hour from endtime) * 60 + extract(minute from endtime))
 - (extract(hour from starttime) * 60 + extract(minute from starttime)) ) / 
60 AS LengthInHours

FROM WALKERS, DOGS, CUSTOMERS, JOBS

WHERE DOGS.OwnerPhoneNum = CUSTOMERS.phonenumber AND Walkers.PhoneNumber = 
JOBS.WalkerPhoneNum AND CUSTOMERS.phonenumber = JOBS.customerphonenum;

一些表格元素看起来像：

| WALKERNAME    | DOGNAME | LENGTHINHOURS
| Francis, Kay  | Daisy   | .5
| Fowler, Rex   | Coco    | 1
| Carlson, Bell | Axel    | .5
| Carlson, Bell | Axel    | 1

贝尔卡尔森的两次出场是我的问题所在。我想总结每个人遛狗的时间，而不是他们单独的时间，看起来像：

| Carlson, Bell | Axel | 1.5

我相信我必须创建某种类型的连接或子查询来将它们汇总在一起，但我无法实现任何有效的方法。

标签： sqloraclesum

解决方案

我能否指出，您确定步行时间长度的方法有点奇怪，如果有步行者在午夜遛狗的情况（不考虑天数），实际上就被打破了？如果endtime和starttime是 Oracle DATEs，那么您可以简单地使用

(endtime - starttime) * 24

Oracle 的日期算法以天表示；乘以 24 得到小时数。

我建议在进行聚合后转换为数小时：

SELECT walkername, dogname, SUM(walktime) * 24 FROM (
    SELECT w.lastname || ', ' || w.firstname AS walkername
         , d.name AS dogname, endtime - starttime AS walktime
      FROM walkers w INNER JOIN jobs j
        ON w.phonenumber = j.walkerphonenum
     INNER JOIN customers c
        ON c.phonenumber = j.customerphonenum
     INNER JOIN dogs d
        ON d.ownerphonenum = c.phonenumber
) GROUP BY walkername, dogname;

如果starttimeand endtimeare TIMESTAMPs 那么我建议将它们转换DATE为使算术变得容易：

SELECT walkername, dogname, SUM(walktime) * 24 FROM (
    SELECT w.lastname || ', ' || w.firstname AS walkername
         , d.name AS dogname
         , CAST(endtime AS DATE) - CAST(starttime AS DATE) AS walktime
      FROM walkers w INNER JOIN jobs j
        ON w.phonenumber = j.walkerphonenum
     INNER JOIN customers c
        ON c.phonenumber = j.customerphonenum
     INNER JOIN dogs d
        ON d.ownerphonenum = c.phonenumber
) GROUP BY walkername, dogname;

希望这可以帮助。

推荐阅读