kn3l's questions

WITH trips_by_day AS
                      (
                      SELECT DATE(trip_start_timestamp) AS trip_date,
                          COUNT(*) as num_trips
                      FROM `bigquery-public-data.chicago_taxi_trips.taxi_trips`
                      WHERE trip_start_timestamp >= '2016-01-01' AND trip_start_timestamp < '2018-01-01'
                      GROUP BY trip_date
                      ORDER BY trip_date
                      )
                      SELECT trip_date,
                          avg(num_trips)
                          OVER (
                               order by trip_date
                               rows between 15 preceding and 15 following
                               ) AS avg_num_trips
                      FROM trips_by_day

谁能给我解释一下的意思rows between 15 preceding and 15 following？

SELECT TOP 1000 [LEAD_CO_MNE]
      ,CASE
         WHEN CURRENCY <> 'KHR' THEN  AMOUNT_FCY
         ELSE AMOUNT_LCY
         END 'Amount' --I want to split into type of amount debit or crdit
     ,CURRENCY

如果 > 0 贷方或借方，我如何将“金额”拆分为 2 列debit并credit基于条件。

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT DISTINCT
       (
         SELECT ForeignCurrencyBal
         FROM [InsightWarehouse].[dbo].[v_Account] AS subA
         WHERE BusinessDate=['2019-02-01','2019-01-31',etc..]
               AND 
               subA.AccountNum = STMT.ACCOUNT_NUMBER
       ) AS Balance,
       ACCOUNT_NUMBER,
       CASE WHEN STMT.CRF_TYPE='CREDIT' 
            THEN STMT.AMOUNT_LCY
            ELSE NULL
       END as 'CreditAmount',
       CASE WHEN STMT.CRF_TYPE='DEBIT' 
            THEN STMT.AMOUNT_LCY
            ELSE  NULL
       END as 'DebitAmount',
       STMT.BOOKING_DATE,
       STMT.VALUE_DATE,
       CRF_TYPE
FROM [InsightLanding].[dbo].[v_STMT_ENTRY] AS STMT 
     LEFT JOIN [InsightWarehouse].[dbo].[v_Account] AS A 
     ON a.AccountNum = STMT.ACCOUNT_NUMBER
WHERE STMT.MIS_DATE='2019-02-01' 
      AND 
      STMT.ACCOUNT_NUMBER='14498478'

在这一行 WHERE BusinessDate=['2019-02-01','2019-01-31',etc..]，这个列表可以通过子查询得到。

我想把条件放在前一天，这意味着此列表中的第二项。像这样的

BusinessDate='2019-01-31'

我有 T1 作为表 1

----------------
field1 | field2
---------------
 val1  | val2

我有 T2 作为表 2

----------------
field1 | field2
---------------
 val11  | val22

我想写一个可以产生这样结果的查询

 field1 | field2 | T1.T2 | 
 ------------------------|
 val11  | val22  | T2    | 
 val1   | val2   | T1    | 

SELECT 
    ROW_NUMBER() OVER(ORDER BY c.FullName ASC) AS No#,    
    a.AcctBranchName, 
    c.FullName,
    c.BirthDate,
    c.Age,
    a.StartDate as 'Account Start Date',
    c.CustStartDate as 'Customer Start Date',
    c.CustomerNum,
    a.Currency,
    a.AccountNum,
    a.ProductType as 'Product Name',
    a.Balance as 'Balance KHR',
    a.BalanceUSDEquiv as 'Balance USA',
    aa.ClosedDate,

    CASE 
        WHEN (a.BalanceUSDEquiv*3 >= 200000) then '200,000'
        ELSE a.BalanceUSDEquiv*3 end as 'Sum Insure in USD',
    CASE 
        WHEN (a.BalanceUSDEquiv*3 > 200000) then 200000*(0.1/100)/12 
        ELSE a.BalanceUSDEquiv*3*(0.1/100)/12 end as 'Premium to be paid'

FROM         
     v_Account AS a 
LEFT join v_Customer AS c 
    ON c.CustomerID = a.CustomerID AND c.Businessdate = a.Businessdate

WHERE     
    a.AccountNum IN ( 
        SELECT  aa.AccountNum FROM v_Account AS aa WHERE   
         aa.Category = 'Deposit' AND aa.ProductCode IN ('6003', '6253') 
        AND aa.Businessdate='2018-12-06' AND aa.ClosedDate is not NULL
    )  
    AND a.Category = 'Deposit' 
    AND a.ProductCode IN ('6003', '6253')
    AND c.Businessdate='2018-12-05'

我如何aa.ClosedDate从我的子查询或任何替代解决方案中选择我可以获得的 ClosedDate 2018-12-06。

例子。帐号：123456 在 2018-12-06 关闭（关闭意味着没有余额）所以为了获得这个帐号的余额，我需要在 2018-12-05 查找这个（在 1 天之前这个帐户被关闭) ，然后我可以获得余额以用于我的计算目的。

SELECT 
      CurrencyCodeFrom,
      CurrencyCodeTo, 
      MidRevalRate,
      MultiplierRate,
      RevalRate,
      CurrencyMarketCode,
      Added, 
      Modified, 
      UpdateReason
FROM 
     [InsightWarehouse].[dbo].[DimCurrency] as dc
 INNER  JOIN 
     [InsightWarehouse].[dbo].[FactCurrency] as fc 
     ON fc.CurrencyId = dc.CurrencyId
     AND fc.BusinessDate  = '2018-11-27'
     AND ( CurrencyCodeFrom <> 'KHR' OR CurrencyCodeTo <> 'KHR')

结果

CurrencyCodeFrom    CurrencyCodeTo  MidRevalRate    MultiplierRate  RevalRate   CurrencyMarketCode
THB                 KHR             122.500000000   122.500000000   1.000000000 1
THB                 KHR             122.750000000   122.750000000   1.000000000 10
THB                 KHR             122.500000000   122.500000000   1.000000000 3
THB                 KHR             122.750000000   122.750000000   1.000000000 5
THB                 KHR             122.750000000   122.750000000   1.000000000 7
THB                 KHR             122.750000000   122.750000000   1.000000000 9
USD                 KHR             4035.000000000  4035.000000000  1.000000000 1
USD                 KHR             4047.500000000  4047.500000000  1.000000000 10
USD                 KHR             4035.000000000  4035.000000000  1.000000000 3
USD                 KHR             4047.500000000  4047.500000000  1.000000000 5
USD                 KHR             4047.500000000  4047.500000000  1.000000000 7
USD                 KHR             4047.500000000  4047.500000000  1.000000000 9

我想CurrencyMarketCode通过保持分组来对 DESC进行排序CurrencyCodeFrom CurrencyCodeTo

应该

CurrencyCodeFrom    CurrencyCodeTo  MidRevalRate    MultiplierRate  RevalRate   CurrencyMarketCode

THB                 KHR             122.500000000   122.500000000   1.000000000 1
THB                 KHR             122.500000000   122.500000000   1.000000000 3
THB                 KHR             122.750000000   122.750000000   1.000000000 5
THB                 KHR             122.750000000   122.750000000   1.000000000 7
THB                 KHR             122.750000000   122.750000000   1.000000000 9
THB                 KHR             122.750000000   122.750000000   1.000000000 10

USD                 KHR             4035.000000000  4035.000000000  1.000000000 1
USD                 KHR             4035.000000000  4035.000000000  1.000000000 3
USD                 KHR             4047.500000000  4047.500000000  1.000000000 5
USD                 KHR             4047.500000000  4047.500000000  1.000000000 7
USD                 KHR             4047.500000000  4047.500000000  1.000000000 9
USD                 KHR             4047.500000000  4047.500000000  1.000000000 10