sql power bi_在SQL，Power BI和MS Excel中計算移動平均值

2023-10-18 阅读 26 评论 0

摘要：sql power bi In this article, I’m going to talk in detail about the moving average in SQL and how to find out the same in Power BI and Excel. This might be a new topic for many newbies who have started out in this field, but I’m sure this will help anyo

sql power bi

In this article, I’m going to talk in detail about the moving average in SQL and how to find out the same in Power BI and Excel. This might be a new topic for many newbies who have started out in this field, but I’m sure this will help anyone who is trying to work on smoothing averages in SQL or in Power BI.

在本文中，我將詳細討論SQL中的移動平均線以及如何在Power BI和Excel中找到相同的移動平均線。對于這個領域的許多新手來說，這可能是一個新話題，但是我敢肯定，這將對試圖在SQL或Power BI中平滑平均值的任何人有所幫助。

Moving Average in SQL or Power BI, as it goes by the name, is a type of average function that is moving; in other words, it is calculated over a period. This is an important scenario while dealing in finance where often analysts tend to work on smoothing the stock prices, for example, over a period and find out the trend of the prices. This trend then aids in defining whether the average is an upward or downward moving trend. This article will focus on how to calculate the moving average in SQL and then we will look at an example to implement the same in Power BI and Excel as well. It is also known by Rolling Average or Running Average.

顧名思義，SQL或Power BI中的移動平均線是一種正在移動的平均函數。換句話說，它是在一段時間內計算得出的。這是在進行金融交易時的一種重要情況，在這種情況下，分析師通常傾向于在一段時間內努力使股票價格趨于平穩，并找出價格趨勢。然后，該趨勢有助于定義平均值是上升還是下降趨勢。本文將重點介紹如何在SQL中計算移動平均值，然后我們來看一個在Power BI和Excel中也實現同樣的示例。也稱為滾動平均或運行平均。

For the sake of this article, we will try to analyze the share price of Facebook, the data of which is easily available to download from yahoo finance website. I have downloaded the data for the last 5 years and saved it in a CSV file.

為了本文的目的，我們將嘗試分析Facebook的股價，該數據很容易從yahoo金融網站上下載。我已經下載了過去5年的數據，并將其保存在CSV文件中。

Facebook Stock Data (Yahoo Finance) to calculate Moving Average in SQL

SQL中的移動平均線 (Moving Average in SQL)

To begin with, let us first import the CSV file into SQL Server so that we can execute our queries accordingly.

首先，讓我們首先將CSV文件導入SQL Server，以便我們可以相應地執行查詢。

Right-click on the Database on which you wish to import the data and select Tasks and then select Import Flat File. Proceed through the dialog boxes and get it imported into the database.

右鍵單擊要在其上導入數據的數據庫 ，然后選擇“ 任務” ，然后選擇“ 導入平面文件” 。繼續執行對話框，然后將其導入數據庫。

Import Flat File Into SQL Server to calculate Moving Average in SQL

Once the data is imported into a table in SQL, we can go ahead and execute our queries. In this article, I’m going to use the WINDOW function in SQL – the OVER clause to calculate the average of every row with consideration of the values from the records before and after that specific row.

將數據導入SQL中的表后，我們可以繼續執行查詢。在本文中，我將使用SQL中的WINDOW函數– OVER子句，并考慮到該特定行之前和之后的記錄中的值，計算每行的平均值。

Now that our data is ready, let us try to calculate a moving average in SQL of 5 days on the closing prices. This means each row will consider 2 rows both before and after the current record, calculate the sum of the price and then divide it by 5. For the first row, since there are no rows before that, the logic remains the same, but only the 2 later records are calculated. Similar is the case for the last record where only the 2 previous records will be calculated since there are no records after that.

現在我們的數據已經準備好，讓我們嘗試用SQL收盤價計算5天的移動平均線。這意味著每行將在當前記錄之前和之后考慮2行，計算價格總和，然后將其除以5。對于第一行，由于在此之前沒有行，因此邏輯保持不變，但僅計算后的2條記錄。最后一條記錄的情況與此類似，因為僅計算前2條記錄，因為此后沒有記錄。

SELECT[Date],[Close],AVG([Close]) OVER (ORDER BY [Date]ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) MovingAverageFiveDay
FROM [FB]

This is not all. This technique can be applied to calculate the moving average in SQL for different periods based on the requirement. If we want to calculate the moving average of 15 days, such that it can include 7 previous and 7 next days, we can just rewrite the query as follows.

這還不是全部。可以根據需要將該技術應用于計算不同時期SQL移動平均值。如果我們要計算15天的移動平均值，以使其可以包括前7天和后7天，則可以按如下所示重寫查詢。

SELECT[Date],[Close],AVG([Close]) OVER (ORDER BY [Date]ROWS BETWEEN 7 PRECEDING AND 7 FOLLOWING) MovingAverageFifteenDay
FROM [FB]

As you can see in the figure above, the average is now based upon 7 previous and 7 next records, including the current one.

正如您在上圖中所看到的，現在的平均值是基于前7條和后7條記錄（包括當前記錄）。

Power BI中的移動平均線 (Moving Average in Power BI)

Power BI as we all know is one of the most fantastic tools in the visualization world. One of the main advantages of using Power BI is that it helps us with some pre-defined calculations, which we can create just with a few clicks without writing a single line of DAX. Of course, you can write your own DAX in case you want to customize; however, the New Quick Measure really does the tough job.

眾所周知，Power BI是可視化領域中最出色的工具之一。使用Power BI的主要優點之一是，它可以幫助我們進行一些預定義的計算，我們只需單擊幾下就可以創建這些計算，而無需編寫一行DAX。當然，您可以編寫自己的DAX以防自定義；但是， 新的快速衡量確實確實很難。

Let us first fetch the data into the Power BI data model. Select Get Data and then from SQL Server. Provide the necessary details and get the data imported into the data model. Click Load once completed and the data will be imported.

讓我們首先將數據提取到Power BI數據模型中。選擇獲取數據 ，然后從SQL Server中選擇 。提供必要的詳細信息，并將數據導入到數據模型中。完成后單擊“ 加載” ，數據將被導入。

Once the data is imported, click on the “Line and Clustered Column Chart” and drag and drop the Date to the Axis and the Close to the Column Values pane. Also, an important thing to note here is that since the dataset has five years of data, we will only visualize it by months and remove the Day from the hierarchy.

導入數據后，單擊“ 折線和聚簇柱形圖 ”，然后將“ 日期 ”拖放到“ 軸”和“ 靠近 列值”窗格中。另外，這里要注意的重要一點是，由于數據集具有五年的數據，因此我們只能按月可視化它，并從層次結構中刪除“ 天 ”。

Once the visualization is ready, the next step is to create the new calculated measure that will store the moving average. Right-click the Date hierarchy and select New Quick Measure from the context menu.

可視化準備就緒后，下一步就是創建新的計算得出的度量，該度量將存儲移動平均值。右鍵單擊“ 日期”層次結構，然后從上下文菜單中選擇“ 新建快速度量 ”。

In the new dialog box that appears, select the Calculation as Rolling Average and the Base Value as Average of Close (since we want to calculate the MA based on the closing price). Select the period for which the rolling average is to be calculated. In this example, I’m going to calculate it based on 7 months before and after the actual month, basically a fifteen-month moving average. Click OK once done.

在出現的新對話框中，選擇“ 計算”作為“ 滾動平均值 ”，然后選擇“ 基值”作為“收盤平均值” （ 因為我們要根據收盤價計算均線 ）。選擇要計算滾動平均值的時間段。在此示例中，我將基于實際月份前后的7個月（基本上是15個月的移動平均值）進行計算。完成后，單擊“ 確定” 。

Once you finish defining the configuration, a new measure will be created by the name “Average of Close Rolling Average”. You can also see the underlying DAX formula that the Power BI engine automatically generates for us.

完成定義配置后，將使用名稱“ Close Rolling Average的平均值 ”創建一個新度量。您還可以查看Power BI引擎自動為我們生成的基礎DAX公式。

Once the new measure is created, just drag and drop it into the Line Values pane. You can see a new line will be displayed on the chart which will give us the running average of the fifteen months.

創建新度量后，只需將其拖放到“ 線值”窗格中即可。您會看到一條新線將顯示在圖表上，這將為我們提供15個月的運行平均值。

Excel中的移動平均線 (Moving Average in Excel)

Excel has always been a go-to tool for many people for a long time now, and hence, it is also extremely important to be able to perform such kind of calculations with ease. Excel provides an add-in for Data Analysis purposes, which needs to be imported into Excel before performing the analysis.

Excel長期以來一直是許多人的首選工具，因此，能夠輕松執行此類計算也非常重要。 Excel提供了一個用于數據分析的加載項，需要在執行分析之前將其導入Excel。

Open the CSV file that you have downloaded earlier from the yahoo portal and save it as an excel file. Click on Home on the ribbon bar and then select Options.

打開您先前從yahoo門戶下載的CSV文件，并將其另存為excel文件。單擊功能區欄上的主頁，然后選擇選項。

In the Excel Options dialog box that opens, select Excel Add-ins against Manage and click on Go.

在打開的“ Excel選項”對話框中，針對“ 管理”選擇“ Excel加載項” ，然后單擊“ 轉到” 。

In the Add-ins dialog box, select the Analysis ToolPak and click OK.

在“ 加載項”對話框中，選擇“ 分析工具庫” ，然后單擊“ 確定” 。

From the Data tab, click on the Data Analysis button under the Analysis pane in the ribbon.

在“ 數據”選項卡中，單擊功能區“ 分析”窗格下的“ 數據分析”按鈕。

Select Moving Average and click OK.

選擇移動平均線 ，然后單擊確定。

In the next dialog box that appears, select the Input Range from the column values in the Excel sheet. Define how many intervals should the rolling average has to be calculated. For this example, I want to smooth the average for 60 intervals. Click OK once done. This will add a new column, which is defined by the Output Range cells. Let the name for this column be MovingAverage.

在出現的下一個對話框中，從Excel工作表的列值中選擇“ 輸入范圍 ”。定義應該計算滾動平均值的間隔時間 。對于此示例，我想對60個間隔的平均值進行平滑處理。完成后，單擊“ 確定” 。這將添加一個新列，該列由“ 輸出范圍”單元格定義。讓此列的名稱為MovingAverage 。

Once the new field is added, select the entire dataset and click on the Insert tab. Select the Line Chart, as shown in the figure below and plot it on the sheet.

添加新字段后，選擇整個數據集，然后單擊“ 插入”選項卡。選擇折線圖，如下圖所示，并將其繪制在圖紙上。

Two lines will be displayed in the chart, as shown in the figure below. The blue line represents the original value from the dataset, whereas the orange line represents the rolling average of the selected periods. In this way, we can easily smooth out the original curve and plot a rolling average calculation in Excel.

圖表中將顯示兩行，如下圖所示。藍線表示數據集的原始值，而橙線表示所選期間的滾動平均值。這樣，我們可以輕松平滑原始曲線并在Excel中繪制滾動平均值計算。

結論 (Conclusion)

In this article, we have seen what a moving average in SQL is and how to calculate one. I have also explained how to implement the same with examples in Power BI and Excel.

在本文中，我們了解了什么是SQL移動平均值以及如何計算平均值。我還解釋了如何使用Power BI和Excel中的示例來實現相同的功能。