sql - 矢印 - ドコモ キッズ携帯 裏ワザ




複数の子供のペアリングに基づく親の数 (3)

以下の例では、バーの場所ごとの食材の入手可能性に基づいて、飲み物の数を数えようとしています。

以下の例に示すように、さらに明確にするために、以下の図で強調表示されている数値に基づいてください。 私は私が6/30/2018に1つのマルガリータを作ることができることを知っています(私がその場所に物資を出荷するならばDCかFLのどちらかで)。

データテーブルの例

上記の関連データを入力するには、以下のコードを使用してください。

    CREATE TABLE #drinks 
    (
        a_date      DATE,
        loc         NVARCHAR(2),
        parent      NVARCHAR(20),
        line_num    INT,
        child       NVARCHAR(20),
        avail_amt   INT
    );

INSERT INTO #drinks VALUES ('6/26/2018','CA','Long Island','1','Vodka','7');
INSERT INTO #drinks VALUES ('6/27/2018','CA','Long Island','2','Gin','5');
INSERT INTO #drinks VALUES ('6/28/2018','CA','Long Island','3','Rum','26');
INSERT INTO #drinks VALUES ('6/26/2018','DC','Long Island','1','Vodka','15');
INSERT INTO #drinks VALUES ('6/27/2018','DC','Long Island','2','Gin','18');
INSERT INTO #drinks VALUES ('6/28/2018','DC','Long Island','3','Rum','5');
INSERT INTO #drinks VALUES ('6/26/2018','FL','Long Island','1','Vodka','34');
INSERT INTO #drinks VALUES ('6/27/2018','FL','Long Island','2','Gin','14');
INSERT INTO #drinks VALUES ('6/28/2018','FL','Long Island','3','Rum','4');
INSERT INTO #drinks VALUES ('6/30/2018','DC','Margarita','1','Tequila','6');
INSERT INTO #drinks VALUES ('7/1/2018','DC','Margarita','2','Triple Sec','3');
INSERT INTO #drinks VALUES ('6/29/2018','FL','Margarita','1','Tequila','1');
INSERT INTO #drinks VALUES ('6/30/2018','FL','Margarita','2','Triple Sec','0');
INSERT INTO #drinks VALUES ('7/2/2018','CA','Cuba Libre','1','Rum','1');
INSERT INTO #drinks VALUES ('7/8/2018','CA','Cuba Libre','2','Coke','5');
INSERT INTO #drinks VALUES ('7/13/2018','CA','Cuba Libre','3','Lime','14');
INSERT INTO #drinks VALUES ('7/5/2018','DC','Cuba Libre','1','Rum','0');
INSERT INTO #drinks VALUES ('7/19/2018','DC','Cuba Libre','2','Coke','12');
INSERT INTO #drinks VALUES ('7/31/2018','DC','Cuba Libre','3','Lime','9');
INSERT INTO #drinks VALUES ('7/2/2018','FL','Cuba Libre','1','Rum','1');
INSERT INTO #drinks VALUES ('7/19/2018','FL','Cuba Libre','2','Coke','3');
INSERT INTO #drinks VALUES ('7/17/2018','FL','Cuba Libre','3','Lime','2');
INSERT INTO #drinks VALUES ('6/30/2018','DC','Long Island','3','Rum','4');
INSERT INTO #drinks VALUES ('7/7/2018','FL','Cosmopolitan','5','Triple Sec','7');

予想される結果は次のとおりです。

期待される結果に見られるように、子供たちが交換可能であることに注意してください。 例えば、7/7/18にTriple Secが国際的な飲み物に到着しました。 しかし子供もラム酒であるので、それはFLのためのMargaritasの有用性を変えます。

また、06/30と06/31の両方のキューバリブレのためのDC地域への更新ではありません。

部品が交換可能であること、そしてまた新しいアイテムが到着するたびにそれが今以前にどんなアイテムでも利用可能にすることを考慮に入れてください。

最後に - 私は子供の空室状況だけに基づいて場所に関係なくキットの空室状況を示す別の列を追加することができたら素晴らしいでしょう。 例 DCに子供#3がいてFLに子供がいない場合、彼らは別の場所の在庫に基づいて飲み物を作るのに十分な在庫があるとFLは判断できます。


SELECT ( SELECT MAX(d2.a_date) FROM #drinks AS d2 WHERE d2.parent = d.parent AND d2.loc = d.loc) AS a_date ,d.loc ,d.parent ,SUM(d.avail_amt) AS [avail_amt(SUM)] ,COUNT(d.avail_amt) AS [avail_amt(COUNT)] FROM #drinks AS d GROUP BY d.loc ,d.parent ORDER BY a_date


これがまさにあなたが探しているものだとは思わないでください...多分それは役立つでしょう。

SELECT DISTINCT #drinks.loc,#drinks.parent,avail.Avail
FROM #drinks
LEFT OUTER JOIN (
SELECT DISTINCT #drinks.parent, MIN(availnow.maxavailnow / line_num) 
OVER(PARTITION BY parent) as Avail
FROM #drinks
LEFT OUTER JOIN (
            SELECT #drinks.child,SUM(avail_amt) maxavailnow
            FROM #drinks
            LEFT OUTER JOIN (SELECT MAX(a_date) date,loc,child FROM #drinks GROUP BY loc,child) maxx ON #drinks.loc = maxx.loc AND #drinks.child = maxx.child AND maxx.date = #drinks.a_date
            GROUP BY #drinks.child
            ) availnow ON #drinks.child = availnow.child
            ) avail ON avail.parent = #drinks.parent

私はクエリを書くのを助けるためにいくつかの追加のテーブルを作成しました、しかしあなたが望むならこれらは#drinksテーブルから生成されることができました:

CREATE TABLE #recipes 
(
    parent      NVARCHAR(20),
    child       NVARCHAR(20)
);

INSERT INTO #recipes VALUES ('Long Island', 'Vodka');
INSERT INTO #recipes VALUES ('Long Island', 'Gin');
INSERT INTO #recipes VALUES ('Long Island', 'Rum');
INSERT INTO #recipes VALUES ('Maragrita', 'Tequila');
INSERT INTO #recipes VALUES ('Maragrita', 'Triple Sec');
INSERT INTO #recipes VALUES ('Cuba Libre', 'Coke');
INSERT INTO #recipes VALUES ('Cuba Libre', 'Rum');
INSERT INTO #recipes VALUES ('Cuba Libre', 'Lime');
INSERT INTO #recipes VALUES ('Cosmopolitan', 'Cranberry Juice');
INSERT INTO #recipes VALUES ('Cosmopolitan', 'Triple Sec');

CREATE TABLE #locations 
(
    loc      NVARCHAR(20)
);

INSERT INTO #locations VALUES ('CA');
INSERT INTO #locations VALUES ('FL');
INSERT INTO #locations VALUES ('DC');

クエリは次のようになります。

DECLARE @StartDateTime DATETIME
DECLARE @EndDateTime DATETIME

SET @StartDateTime = '2018-06-26'
SET @EndDateTime = '2018-07-31';

--First, build a range of dates that the report has to run for
WITH DateRange(a_date) AS 
(
    SELECT @StartDateTime AS DATE
    UNION ALL
    SELECT DATEADD(d, 1, a_date)
    FROM   DateRange 
    WHERE  a_date < @EndDateTime
)
SELECT a_date, parent, loc, avail_amt
FROM   (--available_recipes_inventory
        SELECT a_date, parent, loc, avail_amt,
               LAG(avail_amt, 1, 0) OVER (PARTITION BY loc, parent ORDER BY a_date) AS previous_avail_amt
        FROM   (--recipes_inventory
                SELECT a_date, parent, loc, 
                       --The least amount of the ingredients for a recipe is the most 
                       --amount of drinks we can make for it
                       MIN(avail_amt) as avail_amt
                FROM   (--ingredients_inventory
                        SELECT dr.a_date, r.parent, r.child, l.loc, 
                               --Default ingredients we don't have with a zero amount
                               ISNULL(d.avail_amt, 0) as avail_amt
                        FROM   DateRange dr CROSS JOIN
                               #recipes r CROSS JOIN
                               #locations l OUTER APPLY
                               (
                                --Find the total amount available for each 
                                --ingredient at each location for each date
                                SELECT SUM(d1.avail_amt) as avail_amt
                                FROM   #drinks d1
                                WHERE  d1.a_date <= dr.a_date
                                AND    d1.loc = l.loc
                                AND    d1.child = r.child
                               ) d
                        ) AS ingredients_inventory
                GROUP BY a_date, parent, loc
               ) AS recipes_inventory
        --Remove all recipes that we don't have enough ingredients for
        WHERE  avail_amt > 0 
       ) AS available_recipes_inventory
--Selects the first time a recipe has enough ingredients to be made
WHERE  previous_avail_amt = 0 
--Selects when the amount of ingredients has changed
OR     previous_avail_amt != avail_amt 
ORDER BY a_date
--MAXRECURSION needed to generate the date range
OPTION (MAXRECURSION 0)
GO

最も内側のSELECTは、場所、原料、日付、および使用可能な金額からなる擬似在庫テーブル(components_inventory)を作成します。 特定の日付の場所で原料が入手できない場合は、ゼロが使用されます。

次のSELECTクエリでは、各レシピを各場所/日付にいくつ作成できるかがわかります(これもゼロです)。

次のSELECTクエリは、前の日に各ロケーションの各レシピをいくつ作成できるかを収集するために必要な中間テーブルです(作成できなかった飲み物も削除します)。

そして最後に、一番外側のSELECTクエリは前日のデータを使用して、作成できる特定の各レシピの数量がいつ変更されたかを調べます。

このクエリはあなたのテーブルとはわずかに異なる数を生成しますが、私はあなたのものが間違っているからだと思いますか? フロリダを例にとると、余分なラム酒が7月2日に入ってくるので、作ることができるロングアイランドの数は5まで上がります。そして2キューバリブレは19日までに作ることができます。

結果:

+------------+-------------+-----+-----------+
| a_date     | parent      | loc | avail_amt |
+------------+-------------+-----+-----------+
| 2018-06-28 | Long Island | DC  | 5         |
| 2018-06-28 | Long Island | CA  | 5         |
| 2018-06-28 | Long Island | FL  | 4         |
| 2018-06-30 | Long Island | DC  | 9         |
| 2018-07-01 | Maragrita   | DC  | 3         |
| 2018-07-02 | Long Island | FL  | 5         |
| 2018-07-07 | Maragrita   | FL  | 1         |
| 2018-07-13 | Cuba Libre  | CA  | 5         |
| 2018-07-19 | Cuba Libre  | FL  | 2         |
| 2018-07-31 | Cuba Libre  | DC  | 9         |
+------------+-------------+-----+-----------+




outer-apply