title: using window function sql inside pyspark
body: <p>I have data like the example data below.  I’m trying to create a new column in my data using PySpark that would be the category of the first event for a customer based on the timestamp.  Like the example output data below.  </p>
  
  <p>I have an example below of what I think would accomplish it using a window function in sql. </p>
  
  <p>I’m pretty new to PySpark.  I understand you can run sql inside of PySpark.  I’m wondering if I have the code correct below to run the sql window function in PySpark.  That is I’m wondering if I can just paste the sql code inside of spark.sql, as I have below.    </p>
  
  <p>Input:</p>
  
  <pre><code>eventid customerid category timestamp
  1       3          a        1/1/12
  2       3          b        2/3/14
  4       2          c        4/1/12
  </code></pre>
  
  <p>Output:</p>
  
  <pre><code>eventid customerid category timestamp first_event
  1       3          a        1/1/12    a
  2       3          b        2/3/14    a
  4       2          c        4/1/12    c
  </code></pre>
  
  <p>window function example:</p>
  
  <pre><code>select eventid, customerid, category, timestamp 
  FIRST_VALUE(catgegory) over(partition by customerid order by timestamp) first_event
  from table
  
  
  
  # implementing window function example with pyspark
  
  PySpark:
  # Note: assume df is dataframe with structure of table above
  # (df is table)
  
  from pyspark.sql import SparkSession
  
  spark = SparkSession.builder.appName(“Operations”).getOrCreate()
  
  # Register the DataFrame as a SQL temporary view
  
  df.createOrReplaceView(“Table”)
  
  sql_results = spark.sql(“select eventid, customerid, category, timestamp 
                  FIRST_VALUE(catgegory) over(partition by customerid order by                timestamp) first_event
                  from table”)
  
  # display results
  sql_results.show()
  </code></pre>