如何在SQLite3中让计算后的列值保留3位小数？

我需要将一列数值除以另一列数值，把计算结果存入新列并保留千分位（3位小数）。

我尝试的SQL代码如下：

%%sql
--#找出平均每班次接待顾客最多的5名收银员
WITH customers_seen AS (SELECT E.NAME,
                               E.EMPLOYEE_ID,
                               E.POSITION,
                               COUNT(DISTINCT T.TRANSACTION_ID) AS CUSTOMERS_SERVICED,
                               S.STORE_BRANCH
                        FROM TRANSACTIONS T
                        JOIN EMPLOYEES E
                        ON E.EMPLOYEE_ID = T.CASHIER_ID
                        JOIN STORES S
                        ON S.STORE_ID = T.STORE_ID
                        GROUP BY E.NAME
                        ORDER BY CUSTOMERS_SERVICED DESC),

number_of_shifts AS (SELECT E.NAME,
                            E.EMPLOYEE_ID,
                            COUNT(DISTINCT T.DATE) AS NUMBER_OF_SHIFTS,
                            E.SHIFT,
                            E.POSITION
                     FROM TRANSACTIONS T
                     JOIN EMPLOYEES E
                     ON E.EMPLOYEE_ID = T.CASHIER_ID
                     GROUP BY E.NAME
                     HAVING E.POSITION = 'cashier'
                     ORDER BY NUMBER_OF_SHIFTS DESC)

SELECT n.NAME,
       c.CUSTOMERS_SERVICED,
       n.NUMBER_OF_SHIFTS,
       ROUND(printf("%.1f",c.CUSTOMERS_SERVICED/n.NUMBER_OF_SHIFTS),4) AS CUSTOMERS_SERVICED_PER_SHIFT
FROM number_of_shifts n
JOIN customers_seen c
ON n.EMPLOYEE_ID = c.EMPLOYEE_ID
ORDER BY CUSTOMERS_SERVICED_PER_SHIFT DESC
LIMIT 5;

但执行后结果只保留了1位小数，不符合保留3位小数的需求。

问题出在你先用printf("%.1f")把结果限制为1位小数，后续再用ROUND也无法恢复更多小数位。直接调整格式化规则即可：

方法1：使用ROUND函数直接保留3位小数 #

%%sql
--#找出平均每班次接待顾客最多的5名收银员
WITH customers_seen AS (SELECT E.NAME,
                               E.EMPLOYEE_ID,
                               E.POSITION,
                               COUNT(DISTINCT T.TRANSACTION_ID) AS CUSTOMERS_SERVICED,
                               S.STORE_BRANCH
                        FROM TRANSACTIONS T
                        JOIN EMPLOYEES E
                        ON E.EMPLOYEE_ID = T.CASHIER_ID
                        JOIN STORES S
                        ON S.STORE_ID = T.STORE_ID
                        GROUP BY E.NAME
                        ORDER BY CUSTOMERS_SERVICED DESC),

number_of_shifts AS (SELECT E.NAME,
                            E.EMPLOYEE_ID,
                            COUNT(DISTINCT T.DATE) AS NUMBER_OF_SHIFTS,
                            E.SHIFT,
                            E.POSITION
                     FROM TRANSACTIONS T
                     JOIN EMPLOYEES E
                     ON E.EMPLOYEE_ID = T.CASHIER_ID
                     GROUP BY E.NAME
                     HAVING E.POSITION = 'cashier'
                     ORDER BY NUMBER_OF_SHIFTS DESC)

SELECT n.NAME,
       c.CUSTOMERS_SERVICED,
       n.NUMBER_OF_SHIFTS,
       ROUND(c.CUSTOMERS_SERVICED / n.NUMBER_OF_SHIFTS, 3) AS CUSTOMERS_SERVICED_PER_SHIFT
FROM number_of_shifts n
JOIN customers_seen c
ON n.EMPLOYEE_ID = c.EMPLOYEE_ID
ORDER BY CUSTOMERS_SERVICED_PER_SHIFT DESC
LIMIT 5;

方法2：使用printf直接格式化到3位小数 #

%%sql
--#找出平均每班次接待顾客最多的5名收银员
WITH customers_seen AS (SELECT E.NAME,
                               E.EMPLOYEE_ID,
                               E.POSITION,
                               COUNT(DISTINCT T.TRANSACTION_ID) AS CUSTOMERS_SERVICED,
                               S.STORE_BRANCH
                        FROM TRANSACTIONS T
                        JOIN EMPLOYEES E
                        ON E.EMPLOYEE_ID = T.CASHIER_ID
                        JOIN STORES S
                        ON S.STORE_ID = T.STORE_ID
                        GROUP BY E.NAME
                        ORDER BY CUSTOMERS_SERVICED DESC),

number_of_shifts AS (SELECT E.NAME,
                            E.EMPLOYEE_ID,
                            COUNT(DISTINCT T.DATE) AS NUMBER_OF_SHIFTS,
                            E.SHIFT,
                            E.POSITION
                     FROM TRANSACTIONS T
                     JOIN EMPLOYEES E
                     ON E.EMPLOYEE_ID = T.CASHIER_ID
                     GROUP BY E.NAME
                     HAVING E.POSITION = 'cashier'
                     ORDER BY NUMBER_OF_SHIFTS DESC)

SELECT n.NAME,
       c.CUSTOMERS_SERVICED,
       n.NUMBER_OF_SHIFTS,
       printf("%.3f", c.CUSTOMERS_SERVICED / n.NUMBER_OF_SHIFTS) AS CUSTOMERS_SERVICED_PER_SHIFT
FROM number_of_shifts n
JOIN customers_seen c
ON n.EMPLOYEE_ID = c.EMPLOYEE_ID
ORDER BY CUSTOMERS_SERVICED_PER_SHIFT DESC
LIMIT 5;

这两种方法都能让计算结果准确保留3位小数，满足你的需求。

内容的提问来源于stack exchange，提问作者dz1981-bit