Alternating Sign Sequence

JavaScript ES6, 18 bytes

n=>-~(n/3)*(1-n%3)

Turned out very similar to @LeakyNun's answer but I didn't see his until after I posted mine.

Explanation and Ungolfed

-~ is shorthand for Math.ceil, or rounding up:

n =>               // input in var `n`
    Math.ceil(n/3) // Get every 3rd number 1,1,1,2,2,2, etc.
    *
    (1-n%3)        // 1, 0, -1, 1, 0, -1, ...

function f(n){n=i.value;o.value=-~(n/3)*(1-n%3);}

Input: <input id=i oninput="f()"/><br /><br />
Output: <input id=o readable/>

Jelly, 7 bytes

+6d3’PN

Zero-indexed. Test cases here.

Explanation:

+6      Add 6:     x+6
d3      Divmod:    [(x+6)/3, (x+6)%3]
’       Decrement: [(x+6)/3-1, (x+6)%3-1]
P       Product    ((x+6)/3-1) * ((x+6)%3-1)

MarioLANG, 93 81 bytes

one-indexed

Try It Online

;(-))+(-
"============<
>:(![<:![<:)![
 !=#="!#="!=#=
!  < !-< !- <
#==" #=" #=="

Explanation :

we begin by taking the imput

;

wich give us

          v
... 0 0 input 0 0 ...

we then decrement the left byte and increment the right byte with

;(-))+(
=======

we end up with

           v
... 0 -1 input +1 0 ...

we then set up the loop

;(-))+(-
"============<
>  ![< ![<  ![
   #=" #="  #=
!  < !-< !- <
#==" #=" #=="

the loop will go until the memory look like

         v 
... 0 -X 0 +X 0 ...

we then only need to output the result

;(-))+(-
"============<
>:(![<:![<:)![
 !=#="!#="!=#=
!  < !-< !- <
#==" #=" #=="