sql - 获取本月第一个日期的值

Question

我有每个产品库存的每周数据。我想按年月分组并获得每个月的第一个值。换句话说，我想获得每个月的期初库存，而不管该月的哪一天。

+------------+---------+
|   MyDate   | MyValue |
+------------+---------+
| 2018-01-06 |       2 |*
| 2018-01-13 |       7 |
| 2018-01-20 |       5 |
| 2018-01-27 |       2 |
| 2018-02-03 |       3 |*
| 2018-02-10 |      10 |
| 2018-02-17 |       6 |
| 2018-02-24 |       4 |
| 2018-03-03 |       7 |*
| 2018-03-10 |       5 |
| 2018-03-17 |       3 |
| 2018-03-24 |       4 |
| 2018-03-31 |       6 |
+------------+---------+

期望的结果:

+----------------+---------+
| FirstDayOfMonth| MyValue |
+----------------+---------+
| 2018-01-01     |       2 |
| 2018-02-01     |       3 |
| 2018-03-01     |       7 |
+----------------+---------+

我认为这可能行得通，但事实并非如此。

select 
    [product],
    datefromparts(year([MyDate]), month([MyDate]), 1),
    FIRST_VALUE(MyValue) OVER (PARTITION BY [Product], YEAR([MyDate]), MONTH([MyDate]) ORDER BY [MyDate] ASC) AS MyValue
from 
    MyTable 
group by 
    [Product],
    YEAR([MyDate]), MONTH([MyDate])

编辑。谢谢。我问题的重点不是如何获得该月的第一天。我知道有不同的技术。

重点是如何获取月份的第一个值(期初股票)。如果有机会一口气拿到收尾的股票——那就太好了。基于 ROW_NUMBER 的答案不允许一次获得收盘库存，需要两次连接。

接受答案后编辑
请将 John Cappelletti 的回答视为公认答案的替代方案:https://stackoverflow.com/a/53559750/1903793

Answer 1

你真的不需要 GROUP BY如果您选择了窗口函数路线:

SELECT Product, DATEADD(DAY, 1, EOMONTH(MyDate, -1)) AS Month, MyValue
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Product, DATEADD(DAY, 1, EOMONTH(MyDate, -1)) ORDER BY MyDate) AS rn
    FROM t
) AS x
WHERE rn = 1

更新

要获取当月的最后一行，只需执行 UNION ALL <above query>但将 order by 子句更改为 ORDER BY MyDate DESC .这将为您提供每个产品月两行。